Multiple Vlookups

[cjenkins82]

I'm on 2013 and want to do multiple vlookups. I have a list of names on one sheet. i want to match that name against a list of names in several other sheets and return the value in a certain column. i.e if Joe Jones appears in sheet 1 tell me the value in column D, then repeat this for sheet 2 and sheet 3 etc. The person may not appear in every sheet but i don't want the next lookup to fail if this is the case. I have a single vlookup working but can't seem to combine. =VLOOKUP(G2|'G3'!C:O|2|0)
Thanks Charlotte

 

[steve the fish]

You could either nest a few IFERROR formulas together until you have checked each sheet:

=IFERROR(VLOOKUP(G2,Sheet1!C:O,2,0),IFERROR(VLOOKUP(G2,Sheet2!C:O,2,0),VLOOKUP(G2,Sheet3!C:O,2,0)))

or you could try this:

=VLOOKUP(G2,INDIRECT("'"&INDEX({"Sheet1","Sheet2","Sheet3"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!C:C"),G2)>0),0))&"'!C:D"),2,0)

or you could type your sheet names into cells and create a named range then replace {"Sheet1","Sheet2","Sheet3"} with the name such as for example:

=VLOOKUP(G2,INDIRECT("'"&INDEX(mySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&mySheets&"'!C:C"),G2)>0),0))&"'!C:D"),2,0)

Both those formulas require CTRL-SHIFT-ENTER. All depends on how many sheets you have i suppose.

 

[cjenkins82]

Thanks for this.....i've actually now got this formula =IFERROR(VLOOKUP(G2|'G3'!C:O|MATCH(Totals!D1|'G3'!C1:O1|0)|0)|"") but how do i replicate that formula to add the two together as i have sheet G4 too. ive tried =IFERROR(VLOOKUP(G2|'G3'!C:O|MATCH(Totals!D1|'G3'!C1:O1|0)|0)|"")|IFERROR(VLOOKUP(G2|'G4'!|MATCH(Totals!D1|'G4'!C1:O1|0)|0)|"")) but it doesnt work

You could either nest a few IFERROR formulas together until you have checked each sheet:

=IFERROR(VLOOKUP(G2,Sheet1!C:O,2,0),IFERROR(VLOOKUP(G2,Sheet2!C:O,2,0),VLOOKUP(G2,Sheet3!C:O,2,0)))

or you could try this:

=VLOOKUP(G2,INDIRECT("'"&INDEX({"Sheet1","Sheet2","Sheet3"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!C:C"),G2)>0),0))&"'!C:D"),2,0)

or you could type your sheet names into cells and create a named range then replace {"Sheet1","Sheet2","Sheet3"} with the name such as for example:

=VLOOKUP(G2,INDIRECT("'"&INDEX(mySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&mySheets&"'!C:C"),G2)>0),0))&"'!C:D"),2,0)

Both those formulas require CTRL-SHIFT-ENTER. All depends on how many sheets you have i suppose.

 

[steve the fish]

That would be like this:

=IFERROR(VLOOKUP(G2,'G3'!C:O,MATCH(Totals!D1,'G3'!C1:O1,0),0),IFERROR(VLOOKUP(G2,'G4'!C:O,MATCH(Totals!D1,'G4'!C1:O1,0),0),""))