Thread: INdex and Match Array (Excel 2003)

1. INdex and Match Array (Excel 2003)

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

2. Re: INdex and Match Array (Excel 2003)

<P ID="edit" class=small>(Edited by sdckapr on 17-Dec-08 13:21. I misread the question and changed my answer...)</P>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

3. Re: INdex and Match Array (Excel 2003)

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?

TIA

cheers, francis

4. Re: INdex and Match Array (Excel 2003)

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"),S heet2!\$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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•