Forum Discussion
Solved
INDEX MATCH BETWEEN 2 PERCENTAGE in the same columns
greetings
sorry for my English
i would like to have a best function for below image
i want a formula to get the right value based on B15 and C15 input
the percentage is between column 1 and 2
i tried to use this formula =INDEX(B3:G9,MATCH(B15,A3:A9,0),MATCH(C15,B1:F1,MATCH(C15,B2:F2)))
but the output is #N/A
=INDEX(B3:G9,MATCH(B15,A3:A9,0),MATCH(1,(C15>=B2:F2)*(C15<=B1:F1),0))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
I think your equation might work if you add a -1 to that second match and reference row 1:
so it is MATCH(C15,B1:F1,-1)
because that list is decreasing order instead of increasing order.- i tried it and i got same answer #N/A
- not sure why, that is exactly how it should work. I would need to see your actual sheet to see why.
on another note: G1 should be 50 not blank and G2 should be 0 for any formula to work right on values <=50
=INDEX(B3:G9,MATCH(B15,A3:A9,0),MATCH(1,(C15>=B2:F2)*(C15<=B1:F1),0))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
