Index(match...1)

[srizki]

I have invoice amounts in column A and approvers names on corresponding cell in column B, we have more than one approvers depending on the amount in column B.
I arranged the approvers names in column C and the upper limit of amount they can approve in corresponding column D. the last name in the list does not have upper limit, so there is no amount.
I am looking for a formula that sees the amount in column B, MATCH it with upper the limit in Column D and fetch the name from column C. (I can switch the names and amount in column C and D).

I can do it with =IF(AND formula, but there are more than one approvers so I think I will need, INDEX AND MATCH formula with 1.


Please advise.

 

[lhartono]

Try this formula...

[COLOR=#0000cd]=IFERROR(IFERROR(INDEX($C$2:$C$4,MATCH(TRUE,INDEX($D$2:$D$4>=A2,0),)),NA),INDEX($C:$C,COUNTA($C:$C)))[/COLOR]

 

[srizki]

Thanks,
I will try.