Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Help with complex VLOOKUP
Dear experts,
I've inherited a very large set of spreadsheets. Daily, I get data from other spreadsheets, and paste it into this one. And, I run database queries, and paste those in too. Sometimes, we get bad errors, and spend a long time figuring things out. The person who wrote them is not here. She used the VLOOKUP function all over the place. I've tried a very simple VLOOKUP. =VLOOKUP(A27, $B$3:$B$262, 1, FALSE) This means: take the value in cell a27, Search through cells b3 to b262 if found, put the value for a27 into the cell. ----------- But what the other writer did is beyond me. It looks like this: VLOOKUP($R243,'another worksheet'!$A:$Q,Y$2+8,FALSE)*$W243, and VLOOKUP($R243,'Partial ratios'!$A:$O,Y$2,FALSE)*$W243 Just what exactly does this mean??? 'another worksheet'!$A:$Q,Y$2+8, 'another worksheet'!$A:$O,Y$2, What range is it searching? I am hoping to get answers from people who speak from experience. Thanks a lot! |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with complex VLOOKUP
In each case, the search is still in column A of the named sheet ('another
worksheet' or 'Partial ratios'; check the sheet names on the worksheet tabs to figure out which sheet it's searching). The big difference is that instead of returning the value from column A (specified by the 1 in the first equation) of the matching row, it's returning the value from the column specified in cell Y2 (or that cell plus 8). " wrote: Dear experts, I've inherited a very large set of spreadsheets. Daily, I get data from other spreadsheets, and paste it into this one. And, I run database queries, and paste those in too. Sometimes, we get bad errors, and spend a long time figuring things out. The person who wrote them is not here. She used the VLOOKUP function all over the place. I've tried a very simple VLOOKUP. =VLOOKUP(A27, $B$3:$B$262, 1, FALSE) This means: take the value in cell a27, Search through cells b3 to b262 if found, put the value for a27 into the cell. ----------- But what the other writer did is beyond me. It looks like this: VLOOKUP($R243,'another worksheet'!$A:$Q,Y$2+8,FALSE)*$W243, and VLOOKUP($R243,'Partial ratios'!$A:$O,Y$2,FALSE)*$W243 Just what exactly does this mean??? 'another worksheet'!$A:$Q,Y$2+8, 'another worksheet'!$A:$O,Y$2, What range is it searching? I am hoping to get answers from people who speak from experience. Thanks a lot! |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Help with complex VLOOKUP
|
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Help with complex VLOOKUP
It means, in 'plain' English...
Look on the sheet called 'another worksheet' in colums A to Q to find an exact match of the value in cell R243. When found, return the value in the column number (value in Y2) + 8 that corresponds with this value*. Multiply the returned vlaue by the contents of cell W243. *Column A will be column '1', B = '2' etc... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with complex VLOOKUP | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Complex VLOOKUP | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |