If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Help with complex VLOOKUP
|
#4
|
|||
|
|||
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... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Complex VLOOKUP | Domenic | General Discussion | 0 | February 18th, 2005 06:37 PM |
vlookup data hidden within worksheet | Worksheet Functions | 0 | January 26th, 2005 12:09 PM | |
Insert Vlookup into table_array of Vlookup with named range | Denise | Worksheet Functions | 1 | January 24th, 2005 10:49 PM |
complex number | alyot | Using Forms | 3 | November 6th, 2004 09:28 PM |
VLookup Help | Robert | Worksheet Functions | 5 | December 3rd, 2003 12:57 AM |