Multi Lookup - Another Approach

[Airbisk]

Hi,

I have a spreadsheet that uses the formula below to look at four criteria and return the value where all four match. I am now trying to incorporate this into a larger spreadsheet where this formula (adjusted to work) will appear on every line (approx 5000). I have changed it to work but applied it to all lines and it's very very slow to process....I quit after 7%

=LOOKUP(2,1/((FullData!A2:A650=A7)*(FullData!B2:B650=E7)*(FullData!C2:C650=I7)*(FullData!D2650=M7)),FullData!E2:E650)

My question is does anyone know of a better way of doing what the formula does? Any help/suggestions would be great.

Thanks

 

[Gruuuu]

Yes.

Add a column in your FullData sheet (it can be any blank column. Let's assume Z:Z), with this formula (copied to the bottom of your data range)

[b]=A2&"|"&B2&"|"&C2&"|"&D2[/b]

in your main sheet, use this lookup

[b]=index(FullData!$E:$E,match(A7&"|"&E7&"|"&I7&"|"&M7,FullData!$Z:$Z,0))[/b]

Assuming I have read your requirements correctly. Note that your formula requires the use of an Array Formula, and if you have duplicate matches, you will return the last. My setup returns only the first...

If that doesn't meet your requirements, you will need to explain what you're trying to get, and provide a sample of your data with the expected results.

 

[Airbisk]

Perfect!! Making it work for the spreadsheet now and it's doing the trick....so much quicker wow!!

Thanks Gruu

 

[Gruuuu]

Just keep in mind the caveat about multiple matches! If you are certain that your rows will always provide a unique result of field combinations, this will work for you. If you have multiple rows that meet a specific combination, you will want to investigate. (Adding them together is an easy SUMIF, using the same logic)