SOLVED

Excel Lookup function multiple criteria

Copper Contributor

Hi Apologies if this query is already answered but I cant for the life of me find it!

 

I want to interrogate the table for a specific search string generated from multiple drop downs and display the correct result In cell "N2".

 

Is "INDEX MATCH" the best way to do this. any help much appreciated

 

MW

8 Replies

The "Index Match" function is perfect if you have a double side entry table i.e. columns with options A B C D... and rows with options 1 2 3 4... (as excel itself). In that case, if you insert a letter and a number, "index match" will give you the exact cell you need.

 

This obviously works even with something different from letters and numbers, aka you can match Cities from Cities and you get the distance between them, with 0 on the diagonal.

 

Tell us some more details about that "multiple drop downs": how many? And how is structured the sheet with the raw datas?

Hello,

 

please post a data sample. Mock up the expected result, so we know what we are aiming for. You can upload a workbook to the thread here.

Thanks for replys - I've attached a version of the file which should explain what I mean better.

 

At the moment the table isn't very large but could eventually run into hundreds of rows of data to interrogate, hence the need for an intelligent lookup.

 

Much appreciated

 

best response confirmed by Mark Wiper (Copper Contributor)
Solution

Hello,

 

that sample file is really helpful.  I can suggest two approaches:

 

Approach 1: use a helper column where you combine all the lookup criteria into a lookup key. Insert a new column after F and in the new column G enter this formula in row 2 and copy down:

 

=A2&B2&C2&E2

 

It combines all the values to one lookup key. Now you can refer to that lookup key in an Index/Match combination formula in cell M2:

 

=INDEX($F$2:$F$13,MATCH(I2&J2&K2&L2,$G$2:$G$13,0))

 

Or, if you don't want to use the helper column, you can do it all in one step, but the result may take a while to calculate if you have many rows of data. I'm still taking into account that a column G was added, so the cell references are a bit different from your original layout, but you can see it working in the attached file. In cell M3 I use the formula 

 

=INDEX($F$2:$F$13,MATCH(I3&J3&K3&L3,INDEX($A$2:$A$13&$B$2:$B$13&$C$2:$C$13&E2:E13,0),0))

 

File is attached. Let me know if that helped.

Your an absolute star!!!!! Works perfectly......

 

Thanks and much appreciated

 

Mark

Hi - was hoping you could help me again...

 

I have taken your previous advice and incorporated it into attached spreadsheet. 

 

I've then simplified the lookup generator by creating variable drop down lists and removing duplicates with name range formula.

 

I want to take the drop down data validation to next level by enforcing only possible selections that exist in the table. Hope Im making sense

 

Thanks again

Mark

Hi - was hoping you could help me again...

 

I have taken your previous advice and incorporated it into attached spreadsheet. 

 

I've then simplified the lookup generator by creating variable drop down lists and removing duplicates with name range formula.

 

I want to take the drop down data validation to next level by enforcing only possible selections that exist in the table. Hope Im making sense

 

Thanks again

Mark

 
 

Hi!

two variable v-lookup created this video, may pls see if it helps.

Link: https://youtu.be/gSwvCEviqLQ

Thanks,

1 best response

Accepted Solutions
best response confirmed by Mark Wiper (Copper Contributor)
Solution

Hello,

 

that sample file is really helpful.  I can suggest two approaches:

 

Approach 1: use a helper column where you combine all the lookup criteria into a lookup key. Insert a new column after F and in the new column G enter this formula in row 2 and copy down:

 

=A2&B2&C2&E2

 

It combines all the values to one lookup key. Now you can refer to that lookup key in an Index/Match combination formula in cell M2:

 

=INDEX($F$2:$F$13,MATCH(I2&J2&K2&L2,$G$2:$G$13,0))

 

Or, if you don't want to use the helper column, you can do it all in one step, but the result may take a while to calculate if you have many rows of data. I'm still taking into account that a column G was added, so the cell references are a bit different from your original layout, but you can see it working in the attached file. In cell M3 I use the formula 

 

=INDEX($F$2:$F$13,MATCH(I3&J3&K3&L3,INDEX($A$2:$A$13&$B$2:$B$13&$C$2:$C$13&E2:E13,0),0))

 

File is attached. Let me know if that helped.

View solution in original post