INdex and Match Array (Excel 2003)

Topic

New Reply

Hi

In the attached, I have 2 sheets and an array Index and Match formula to return a value in Col D from col A
in sheet2 based on col A and col C of sheet1. using this formula, I can find only extact match but not an approx. match
How do I modify the formula to include finding approx match?

=INDEX(Sheet2!A:A,MATCH(Sheet1!A2&Sheet1!C2,Sheet2!$B$2:$B$5&Sheet2!$C$2:$C$5,0)+1)

From the attached, I want to find the Approval authority for amount 10,000. The above formula give me #N/A
where I want to have a return of Assc+Exec from Sheet2

TIA

regards, francis

Reply | Quote

Replies

(Edited by sdckapr on 17-Dec-08 13:21. I misread the question and changed my answer…)

How about:
=INDEX(Sheet2!A:A,MATCH(Sheet1!A2&TEXT(Sheet1!C2,”????0″),Sheet2!$B$2:$B$5&TEXT(Sheet2!$C$2:$C$5,”????0″))+1)

Steve

Reply | Quote

Hi Steve

Thanks. Great. its works. Would you kindly explain your formula?
if I wanted to add the result showing a blank or other value if there isn’t a match as it is totally out of range,
how do I add that in?

Your help is appreciate
TIA

cheers, francis

Reply | Quote

It is essentially the same as yours only I removed the “,0” indicating a partial match. I explicitly defined the text range to concatenate with the TEXT function otherwise “61000” would match near “610000” since it does a text lookup not a number lookup. Using text function makes it concatenating something like “6 1000” vs “610000” so they are different.

Here is an array if it does not find a match:
=IF(ISNA(MATCH(Sheet1!A2&TEXT(Sheet1!C2,”????0″),Sheet2!$B$2:$B$5&TEXT(Sheet2!$C$2:$C$5,”????0″))),””,INDEX(Sheet2!A:A,MATCH(Sheet1!A2&TEXT(Sheet1!C2,”????0″),Sheet2!$B$2:$B$5&TEXT(Sheet2!$C$2:$C$5,”????0″))+1))

How can one tell if it is “totally out of range”?

Steve

Reply | Quote