I'm following this tutorial for Microsoft Excel and am having a bit of trouble with VLOOKUP. This completely fails to explain WHAT exactly I am supposed to be doing with it.
I can think of a much easier way to do what I need to do, but it insists I need to use VLOOKUP.
Here's the situation:
I need to work out the total cost of something. This is Size of object multiplied by cost. It wants me to use VLOOKUP to find out this for a whole set of different stores. How does the formula work?
Lookup_value : what do I need to look up?
Table_array : I assume I select the table with the price in it.
Col_Index_num: This is the column number with that table, so when I select it, do I first navigate to that? Or will having that in my formula make it not work?
Sorry if that doesn't make sense, this is really frustrating me.
Here's a pic
edit: It's accompanied by this:
Then create columns for the calculated Area Cost, Staffroom Cost and Shipping Cost, plus a
column for the Total Cost (which is the sum of these three). Set up the spreadsheet to carry
out these calculations.
(Use the VLOOKUP function to link the cost multipliers (area, staffroom, shipping) with
each Building Type. Use a Range-Name to define your look-up table.)
From my understanding, VLOOKUP isn't even used for this. Urgh.
Posts
VLOOKUP(Lookup_value, Table_array, Col_Index_num)
Lookup_value - This is the value that you're going to use to find in the first column of the table specified by Table_array.
Col_Index_Num - If Lookup_value is found, then this returns the value in column Col_Index_Num of Table_array on the same row as where Lookup_value was found.
So, using your example, what it looks like you should be doing is:
1.) Using the building type to search through A5:B11 on your second sheet.
2.) If the building type is found, then return the value in the second column of the table specified by A5:B11 (I assume the second column is cost/sq unit?) on the same row as the specified building type.
So your VLOOKUP() should look like:
VLOOKUP($D4, 'Input - Cost Modelling'!$A$5:$B$11, 2)
And your equation would probably be:
=E4 * VLOOKUP($D4, 'Input - Cost Modelling'!$A$5:$B$11, 2)
Edit: Been pondering, and believe that this might be a better explanation:
When you use VLOOKUP(), think of Excel as doing this:
1.) I'm going to go to the first column in the table specified by Table_array.
2.) I'm going to search down the rows until I find a row in that column that matches Lookup_value.
3.) If I don't find anything, I'll return the #N/A error (I think).
4.) If I've found something, then on the same row that I've found something, I will go to the column specified by Col_Index_Num and return that as the result of VLOOKUP().
It's relating to SUMIF. I'll just post the excel file as it's easier to understand what I'm talking about.
http://www.sendspace.com/file/5w05cf
In the Processing 2 tab I'm trying to find out The countries total costs using the sumif function, and then find out the % they contribute. The values there are wrong. I'm not sure what I am doing wrong..
In the three arguments, what am I selecting for it to sum the countries totals?
I'm not certain what you mean but I think I've got a rough idea what you're trying to do.
Are you inputting the formulas manually into each cell or are you cloning them? Because the placement of your $ symbols seems different for each formula in "Processing 2"
What it looks like you want to do is sum across the range of countries you've got listed. However, assuming you cloned the formulae down, the placement of your $ symbols was wrong to give the appropriate result. The $ symbol should be placed directly in front of the figure you don't want changing (either the column letter or the row number, or both).
So in this case you've got a data range you're comparing from C4 to C14, and K4 to K14 for the respective numbers to sum. You want these data ranges in all your cells.
What happens when you clone the formula downwards is that unless you put the $ in front of the number, the numbers in the range will increase as you go down (so the next cell will calculate from C5 to C15, the next C6 to C16 and so on). That's leading your numbers into areas where your spreadsheet is currently blank whilst missing numbers at the top, and throwing off the number (and hence the percentage) for Lesotho. If you look at what you've currently got in each of those cells, you'll see that those numbers have changed like that as they've gone down.
Likewise if you're cloning the formula across and wanted to keep the column letters fixed, you'd want to put the $ symbol in front of the column letters in that formula before you try to clone it.
The formula it looks like you want is:
=SUMIF(Processing!C$4:C$14,'Processing 2'!A3,Processing!K$4:K$14)
Note the placement of the $ symbols.
If you need to test it try putting that in F3 and cloning it down, then put
=F3/Tsales
into G3 and clone that down.
Assuming this is what you're after, the row numbers should now stay fixed and only the country names being compared should change. The total % should now add up to 100% where it didn't before.
http://www.sendspace.com/file/crxnce
I might post back here if I have any other questions, but for the most part I think it works pretty well.
I assume getting rid of those 0's and N/A's would be easy? I heard you can use the function =IF(ISBLANK etc...
Thanks for all the help
edit: There's one more part that I can attempt to do. I'm going to have a good work through it to see if I can figure it out with the things I've learned here first though.