I am looking to do something similar to a Vlookup, but I have 9 sheets through which I want excel to look. Basically, I have a list of names with data that carries through 9 sheets, and then a shorter summary of specific names. I need to get the data on to the summary sheet for each name. Any help would be much appreciated, thanks.
Share
I hope you are using Excel 2007 or later. If not, this solution will not work for you.
Since you are looking to return data from a single sheet, the solution is fairly simple.
Long, but fairly simple.
The formula at the bottom of this post will look through 9 sheets (Sheet2:Sheet10) and return the data from the first sheet where the lookup_value is found. In this example, the formula searches A2:A10 on each sheet and returns a value from B2:B10 on that same sheet. If the lookup_value is not found on any sheet, the formula will return a blank cell. Obviously, the formula will need to be modified to fit your specific situation.
It is nothing more than a Nested IF using the ISNA function to determine if VLOOKUP found the lookup_value on a given sheet.
Perhaps starting with a shorter version would help. This formula searches 2 ranges on the same sheet, just to make the formula easier to read. If the first VLOOKUP returns a #N/A error, meaning that value wasn’t found in A1:A3, the first IF will be TRUE and the next VLOOKUP will be tested. If that VLOOKUP also returns a #N/A error, the second IF will be TRUE and a blank cell will be returned. On the other hand, if any IF does not return a #N/A error, that means the value has been found and the formula will skip to the FALSE argument for that IF and actually do the VLOOKUP, returning the requested value.
The trick it to keep the VLOOKUPs in the correct sheet order so the value_if_false argument for each IF matches up. Basically you count “up” through the sheets for the IF functions and then “down” through the sheets for the actual VLOOKUP’s in the value_if_false arguments.
Let me know if you need a more detailed explanation.
=IF(ISNA(VLOOKUP(C1,$A$1:$B$3,2,0)),
IF(ISNA(VLOOKUP(C1,$A$6:$B$8,2,0)),””,
VLOOKUP(C1,$A$6:$B$8,2,0)),
VLOOKUP(C1,$A$1:$B$3,2,0))
Here is the expanded version to search 9 sheets:
=IF(ISNA(VLOOKUP(B1,Sheet2!$A$2:$B$10,2,0)),
IF(ISNA(VLOOKUP(B1,Sheet3!$A$2:$B$10,2,0)),
IF(ISNA(VLOOKUP(B1,Sheet4!$A$2:$B$10,2,0)),
IF(ISNA(VLOOKUP(B1,Sheet5!$A$2:$B$10,2,0)),
IF(ISNA(VLOOKUP(B1,Sheet6!$A$2:$B$10,2,0)),
IF(ISNA(VLOOKUP(B1,Sheet7!$A$2:$B$10,2,0)),
IF(ISNA(VLOOKUP(B1,Sheet8!$A$2:$B$10,2,0)),
IF(ISNA(VLOOKUP(B1,Sheet9!$A$2:$B$10,2,0)),
IF(ISNA(VLOOKUP(B1,Sheet10!$A$2:$B$10,2,0)),””,
VLOOKUP(B1,Sheet10!$A$2:$B$10,2,0)),
VLOOKUP(B1,Sheet9!$A$2:$B$10,2,0)),
VLOOKUP(B1,Sheet8!$A$2:$B$10,2,0)),
VLOOKUP(B1,Sheet7!$A$2:$B$10,2,0)),
VLOOKUP(B1,Sheet6!$A$2:$B$10,2,0)),
VLOOKUP(B1,Sheet5!$A$2:$B$10,2,0)),
VLOOKUP(B1,Sheet4!$A$2:$B$10,2,0)),
VLOOKUP(B1,Sheet3!$A$2:$B$10,2,0)),
VLOOKUP(B1,Sheet2!$A$2:$B$10,2,0))