I have a workbook with 2 sheets: spg and data

From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg.

I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but this will filter where D is equal to H5. I need to add an additional filter so that it selects the value where D is equal to H5 AND C is equal to G5 (C = G5 AND D = H5)

But apparently I can not search with multiple criteria in VLOOKUP, so how do I do it?


You should be able to do this with CONCATENATE:

in spg add a column to the far left of your table called somthing like ID. Give that column the following formula:

=CONCATENATE(G5,H5)

This will be what you'll be looking for in your VLOOKUP

Next, in your data sheet use the following formula:

=VLOOKUP(CONCATENATE(C5,D5),spg!$D:$J,7,0)

Where C5 and D5 are the values in the data sheet you want and D is the ID column
Note also that I've changed the range and offset to allow for the ID column



Tables
I always suggest this, because it makes life far easier: use tables.

If you don't know about them already: If you aren't going to be giving this to people who use 2003 or earlier you can use the Format as Table function on the HOME tabe to turn your tables into actual data tables that Excel can read. Excel will then be able to use formulas that reference the tables and their columns, so you don't have to reference entire columns to be sure you got everything.

then your formulas would look like this:

=CONCATENATE([@[FirstCell],[@[SecondCell])

=VLOOKUP(CONCATENATE([@[FirstCell],[@[SecondCell]),SPGTable,7,0)