Excel 2007 : Multiple VLOOKUPS or MATCH or both?

**gmcana** · 12-06-2009, 05:11 AM

I have a problem getting a VLOOKUP to work with a match funtion.
I have two tabs in my spreadsheet: one is titled DATA, the other ACTUALS.

What I need:

Lookup supplier (COLUMN AV), plant number (COLUMN D), purchasing group (COLUMN H) and new type (COLUMN AX) in Tab "Actuals", if all four are found [matched] in the other tab "Data" then return savingsplan ID [which is a six digit number] in Column A.

VLOOKUP is not working so well with this one as I have multiple values that have to match before it returns anything.

Does anyone have any ideas? Thanks!

**DonkeyOte** · 12-06-2009, 05:30 AM

In all honesty you should create a Key on DATA sheet of the important fields, eg

Please Login or Register  to view this content.

You can then use a basic INDEX/MATCH approach on ACTUALS sheet

Please Login or Register  to view this content.

That being said there are only 2 valid returns...

(there are other approaches that avoid use of AY on DATA sheet but they are more expensive performance wise when used with relatively large volumes of data)

**gmcana** · 12-06-2009, 06:05 AM

Thanks! I tried it but I'm getting #N/A as a result on the ACTUALS sheet? Maybe I missed something?

**DonkeyOte** · 12-06-2009, 06:13 AM

Not really - as I said earlier - based on your data and your requirements there will be only two valid results returned (rows 270 & 313) - this is because only a handful of rows on DATA have a New Type code assigned.

(on an aside please do not Quote entire posts in your reply - use Post Reply button rather than Quote to respond - or if you do need to quote - quote only those parts necessary to make sense of your reply)

**gmcana** · 12-06-2009, 07:28 AM

You are correct! I fixed the column where new type was missing and now the index is returning an ID where all requirements match!

Excellent solution! Very fast and elegant.

Thank you!