Vlookup multiple sheets

[jane1613]

hi,

Im trying to perform a vlookup across 5 sheets. I have been able to do one easy and two simultaneously using the iferror function but i keep getting errors when i try to expand to more sheets.

im sure im doing something very simple wrong

This is my simple 1 lookup and iferror look up i have used -

=VLOOKUP(A7,'Coffee Bean Shop'!A5:D30,3,FALSE)

=IFERROR(VLOOKUP(A9,'Coffee Bean Shop'!A5:D30,3,FALSE),VLOOKUP(A9,Miles!A5:D20,3,FALSE))

but i cant seem to get more than 2 in one formula to work, Basically i need all these 5 lookups in one,


=VLOOKUP(A6,'Coffee Bean Shop'!A5:D30,3,FALSE)
=VLOOKUP(A6,Redber!A5:D45,3,FALSE)
=VLOOKUP(A6,Miles!A5:D30,3,FALSE)
=VLOOKUP(A6,Rave!A5:D30,3,FALSE)
=VLOOKUP(A6,Pennine!A5:D60,3,FALSE)

Am i going about this wrong?

Any ideas please?

thankyou

[jason.b75]

There is no easy way to perform a lookup across multiple sheets. Given that you only have a small number of sheets, the method that you have tried is probably the easiest way.

=IFERROR(VLOOKUP(A6,'Coffee Bean Shop'!A5:D30,3,FALSE),
IFERROR(VLOOKUP(A6,Redber!A5:D45,3,FALSE),
IFERROR(VLOOKUP(A6,Miles!A5:D30,3,FALSE),
IFERROR(VLOOKUP(A6,Rave!A5:D30,3,FALSE),
IFERROR(VLOOKUP(A6,Pennine!A5:D60,3,FALSE),"No Match")))))

I've split the formula over multiple lines to make it easier for you to follow.

[jane1613]

That great thankyou works perfectly.

[Tony Valko]

You need to put an IFERROR in front of each VLOOKUP.

=IFERROR(VLOOKUP1(...),IFERROR(VLOOKUP2(...),IFERROR(VLOOKUP3(...),etc.