Help with Lookup/match/index with 2 criteria.

**Fizziii** · 04-28-2011, 08:59 AM

\1

I'm trying to do a lookup based on 2 criteria. 1 is a general column, and the other is a value to look up in that column. In the attached screenshot, cells B2:D4 would be a named range "ItemsFound" and cells A2:A4 would be a named range "ItemResults". I've used the next largest integer in the ItemsFound so I can use a match formula with a -1 to find the next smallest value. This way if it's somewhere in between my criteria it can still find it. I'm getting confused as to how to also base it on difficulty.

I think a way to simplify the question would be how would I use a match function to lookup values in a single column of a larger array? My real data has 70+ columns to look through.

**NBVC** · 04-28-2011, 09:27 AM

Name the Range B1:D1 something like Difficulty

and then use another MATCH in the INDEX function for the column.

i.e.

=INDEX(ItemsFound,Match(B9,ItemResults,0),Match(B7,Difficulty,0))

**Marcol** · 04-28-2011, 09:32 AM

What use is that screenshot?

If you are serious about needing help, then begin by helping us to help you.

Post a sample workbook using the forum facilities!

See how in this link
Forum Rules

[EDIT]
@ NBVC How did you manage to see that screenshot? !!!!!

**ChemistB** · 04-28-2011, 09:40 AM

My solution, similar to NBVC's but I couldn't get his to work

=INDEX(ItemResults,MATCH(B8,INDEX(ItemsFound,,MATCH(B7,Difficulty,0)),1))

**NBVC** · 04-28-2011, 09:48 AM

Originally Posted by ChemistB

My solution, similar to NBVC's but I couldn't get his to work

=INDEX(ItemResults,MATCH(B8,INDEX(ItemsFound,,MATCH(B7,Difficulty,0)),1))

Mine wasn't a solution, per se, it was just to show that the OP needed to reference the column Headers to make a "column match".

Your solution is probably what they are looking for

**Fizziii** · 04-28-2011, 10:00 AM

NBVC & Chemist, thank you for your efforts. Like Chemist, I was unable to get NBVC's method to work.
I see how Chemist's is working and it's not doing what I want, but that's because of my own misunderstanding as to how the "less than" results worked.

By changing the values in my table, I will be able to make this work. Thank you both again and have a wonderful day.

NBVC, I see what you were going for by referencing the columns. I also didn't realize I could omit values in the Index function to just get a column/row.