Index Match with Multiple Criteria - Problems

[almugs]

Hi there,

I'm stuck and need some help with getting an Index/Match formula to work.

I have attached my file in case anyone is able to help me out. This shows a snippet of my data. I need to calculate the time target to set based on 2 criteria in cells A3 and B3. The lookup table is in cells A5:C77. For the Amount Criteria (B3) I expect Excel to find the largest value that is less than or equal to the lookup value.

My attempt at an Index Match formula is in C3 - this unfortunately is not showing the desired results. I am obviously making an error in this formula but unfortunately I haven't been able to figure out where I am going wrong.

Hopefully someone can help.

Regards
Almugs

[ImranBhatti]

It is strange about your file. when I download it the red cell is showing your desired result i.e 77 but when I press F2 to get into edit mode then press enter it shows Value error.

[almugs]

Hi,

Thanks for looking. Unfortunately the formula only shows the correct result occasionally. When you go into Edit Mode I believe you lose the curly brackets as the formula was entered as an array formula with CSE. Hence why you see the Value error.

Regards,
Almugs

[bebo021999]

Hope it helps:

=LOOKUP(2,1/(($A$6:$A$9=$A$3)*($B$6:$B$9<=$B$3)),$C$6:$C$9)

[almugs]

The formula I am using is =INDEX($A$6:$C$9,MATCH(1,(A3=$A$6:$A$9)*(B3=$B$6:$B$9)),3) entered as an array formula

[almugs]

Thanks very much. Works like a charm. It looks like Index Match wasn't the right way to go for this

[Czeslaw]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[almugs]

Very impressive formulas both. Trying to understand how they both work
Czeslaw. I got the first formula to work after editing the semi colon to a comma. =max(index(c6:c9*($a$6:$a$9=$a$3)*($b$6:$b$9<=$b$3),0)). However, I couldn't get the second formula working

I am not sure which is the right formula to use - if only I could use both

[FDibbins]

Thanks very much. Works like a charm. It looks like Index Match wasn't the right way to go for this

No, INDEX/MATCH can do this fairly easily. 2 ways...
NON-ARRAY...
=INDEX($C$6:$C$9,MATCH($A$3&$B$3,INDEX($A$6:$A$9&$B$6:$B$9,0)))
ARRAY method...
=INDEX($C$6:$C$9,MATCH($A$3&$B$3,$A$6:$A$9&$B$6:$B$9))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

When you go into Edit Mode I believe you lose the curly brackets as the formula was entered as an array formula with CSE. Hence why you see the Value error.

Correct, you MUST enter array formulas using CTRL SHIFT ENTER

[almugs]

Hi Ford,

Thanks. I am trying to understand how this Index/Match formula works as its obviously a bit different from the format I was using. Yours uses & quite a lot?

I tried the non array formula and it didn't work as intended until I added a match type 1 as below:
=INDEX($C$6:$C$9,MATCH($A$3&$B$3,INDEX($A$6:$A$9&$B$6:$B$9,0),1))
Is this the correct way of doing it?

Thanks again
Al

[FDibbins]

I left out the ,1 but you could add if it you want. I left it out and got what I show below...

	A	B	C	D	E
2	Location	Amount	Time Target
3	Europe	7500	77	77	77
4
5	Location	Amount	Time Target
6	Europe	0	42
7	Europe	3,500.00	56
8	Europe	10,000.00	63
9	Europe	20,000.00	77

C3=INDEX($A$6:$C$9,MATCH(1,(A3=$A$6:$A$9)*(B3=$B$6:$B$9)),3)
ARRAY entered
D3=INDEX($C$6:$C$9,MATCH($A$3&$B$3,INDEX($A$6:$A$9&$B$6:$B$9,0),1))
E3=INDEX($C$6:$C$9,MATCH($A$3&$B$3,INDEX($A$6:$A$9&$B$6:$B$9,0)))

Interesting that yours did not work that way, what was your actual criteria, and what was in the search range?

[almugs]

Hi Ford,

The criteria in your table above should bring back a Time Target of 56 not 77. That's the issue I was encountering.

Regards
Al

[samba_ravi]

C3=lookup(2,1/(($A$6:$A$9=$A3)*($B$6:$B$9<=$B3)),$C$6:$C$9)
Try this

[FDibbins]

Hi Ford,

The criteria in your table above should bring back a Time Target of 56 not 77. That's the issue I was encountering.

Regards
Al

I must have been asleep

Try this ARRAY formula...
=INDEX($C$6:$C$9,MATCH(IF($A$6:$A$9=$A$3,$B$3,0),$B$6:$B$9),1)

[almugs]

Thanks everyone for the multiple solutions. This has been very helpful.