Index/Match for a lookup involving 2 criteria

**OilMan** · 10-28-2011, 02:05 AM

I have a workbook attached for my problem.

I have 2 criteria lookup using an index and match but it doesn't appear to be working quite as I would like. The problem is rather than having my criteria being exactly equal, I need my second criteria to be greater than or equal to a number which has caused some bugs.

It's quite obvious what I'm after if you open up the workbook as I have stated my requirements and conditions. I have even got a section for you to try your own solution so I can compare against my own tries.

Thanks in advance if you have a go at solving this, it's much appreciated!

Oilman.

**TMShucks** · 10-28-2011, 03:17 AM

Try:

=INDEX($C$3:$C$15,MATCH($N3&$O3,$A$3:$A$15&$B$3:$B$15,1))

Only one outcome is different to your expected result ... but I think it is correct.

Regards

**OilMan** · 10-30-2011, 06:23 PM

TMShucks,
Thanks for the reply - that's much appreciated.
Unfortanately, the $N3&$O3 part in the match formula is throwing me off - these are empty cells? (Or if I put the formula into column N it's a circular reference)
Perhaps you could revise the above formula if required? I'm not sure where you put that formula to come to be able to get a match on all of my examples.
Thanks again, mate.

**ConneXionLost** · 10-30-2011, 06:33 PM

Perhaps using:

=INDEX($C$3:$C$15,MATCH($H3&$I3,$A$3:$A$15&$B$3:$B$15,1),1)

confirmed with Ctrl-Shift-Enter.

Cheers,

**Andrew-R** · 10-30-2011, 06:42 PM

N3 and O3 both contain data in the example sheet you posted, with the formula going in P3.

For what it's worth I went with =INDEX(C:C,MAX(INDEX(ROW($A$3:$A$15)*($A$3:$A$15=H4)*($B$3:$B$15<=I4),0)),1)

As with Shucks' formula this gives one results different to your expected outcome, probably the same one.

**OilMan** · 10-30-2011, 06:51 PM

Everyone,
Thanks so much for your help. I just realised that the file on my computer is slightly varied to the one I posted here and didn't realsie some columns were different.

Great job guys this saves me a great deal of time!

Much appreciated to all those who helped. Added reputation to all.

**TMShucks** · 10-30-2011, 06:51 PM

Thought I uploaded this but obviously not.

As Andrew has said, the formula goes in P3 ... "our" test area ... and uses the entries in N3 and O3.

Anyway, take a look

Regards

**OilMan** · 10-30-2011, 07:06 PM

Great, works a treat!
(I'm not sure how to change the title of this thread to solved....)

**ConneXionLost** · 10-30-2011, 07:07 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save