Home > other >  Can't solve problem with index and match in Excel
Can't solve problem with index and match in Excel

Time:09-04

need help. I have this table and I can not solve it. Why is not my formula working? I try to find the price of a ticket. For example, if row is 1 and seat is also 1, so price should be 170.

My formula is:

=INDEX(B11:D13,MATCH(1,INDEX((A19=F11:F13)*(B18=B15:D15),0),0))

enter image description here

Thank you in advance!

CodePudding user response:

As per my comment, you were matching A19 against a bunch of string values instead of the actual integers in A:A. Therefor try:

=VLOOKUP($A19,$A$11:$D$13,MATCH(B$18,$B$10:$D$10) 1)

CodePudding user response:

Match looks for an exact match, so you first need to find the range that you need then find the position of it. The following should work:

=INDEX($B$11:$D$13,MATCH(MAXIFS($A$11:$A$13,$A$11:$A$13,"<="&$A19),$A$11:$A$13,0),MATCH(MAXIFS($B$10:$D$10,$B$10:$D$10,"<="&B$18),$B$10:$D$10,0))
  • Related