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.

I'm a bit confused. I see 3 possible scenarios based on your question and I don't know which one you have:

1 - Data for each name can be found on all 9 sheets and you want all of it brought into the summary sheet.

2 - Data for each name can be found on 1 or more of the 9 sheets and you want all of it brought into the summary sheet.

3 - Data for each name can be found on only 1 sheet, but you don't know which one and you want it brought into the summary sheet.

For scenarios 1 and 2, where data might be found on multiple sheets, how do you want the data to appear on the summary sheet? In one cell? In multiple contigious cells? In a seperate column from each sheet, like a table? Something else?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks for your reply. The scenario that applies here is the third one.... Any ideas? I have 9 sheets to look through. Can it be done?

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))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

thank you very much for this - it is for something at work so i will try it out on monday and let you know how it goes. thank you for the help!

it worked! thank you very very much- really helped me out with this!

Ask Your Question

Weekly Poll