Excel Index & Match to Pull a Value into a cell

Question

My formula: =INDEX(I21:AH45,MATCH(D11,J20:AH20,1),MATCH(C11,I21:I45,1))

I am working with a spreadsheet, screenshot attached. And basically in field C13 I am attempting to pull in the $value based on the matched criteria but I am getting odd numbers and it is not returning the accurate value.

I basically am trying to MATCH D11 (23% in this case) to J20:AH20 (the row containing the different percentages) and then do another MATCH of C11 ($11,000) to column I21:I45 (The money values to match to) Once I have both of these I am trying to get it to pull the value of the field based on those matches.

My array is I21:AH45 (All the possible values to pull back based on MATCHED criteria)

My formula looks correct and is highlighting correctly in excel as far what I am referencing but I am not able to pull the correct value.

In this case $11,000 @ 23% Should equal a price of $2795 BUT it is pulling back $3,895 and I have no idea how it is getting this number as that number is not in my array of choices it could pull back.

I am still working through this to try and figure it out but any guidance would be appreciated.

You can download a copy of the spreadsheet here: Download Spreadsheet

Thank you for any assistance and guidance...!

Answer 1

Looks like you have the row and column swapped. Try:

=INDEX(I21:AH45,MATCH(C11,I21:I45,1),MATCH(D11,J20:AH20,1))