1. ## Match/Index (Excel 2000)

(<!t>[pre]<!/t> and <!t>[/pre]<!/t> tags inserted by HansV to preserve spacing in table - see <!help=19>Help 19<!/help>)

Good Morning,

Could someone please tell me what is wrong with this formula? It keeps returning an N/A. When I step through it, it fails on the "Index"

What I need is for it to evaluate what is in "Urgent Express 1 day" (worksheet)
<pre> Zones
Pounds 2 3 4 5 6 7 8
1 \$20.58 \$22.14 \$23.88 \$24.57 \$27.42 \$30.33 \$34.07
2 \$22.73 \$24.35 \$25.98 \$26.92 \$29.35 \$31.75 \$34.67
3 \$24.28 \$25.97 \$27.37 \$28.17 \$30.68 \$32.60 \$35.17
</pre>

and

The value that is in Cell G6(Zones) and G8(Pounds) of Input Form (worksheet)

=IF(G10=1,INDEX('Urgent Express 1 day'!A3:H153,MATCH(F8,A4:A153,0),MATCH(G6,A3:H3,0) ))

If the value in G6 is 5 and the value in G8 is 3 it should return \$28.17.

Thanks in advance. It's much appreciated.

2. ## Re: Match/Index (Excel 2000)

Your post doesn't make it clear where the area begins, but if I guess correctly, the formula has the following problems:

- It should refer to B4:H153 instead of A3:H153, since that is the data area.
- It should have G8 instead of F8 since you mention G8 in your post.
- It should refer to 'Urgent Express 1 day'!A4:A153 instead of A4:A153, since that is where the index values live
- It should refer to 'Urgent Express 1 day'!B3:H3 instead of A3:H3 since that is where the index values live (and they don't start in column A)

So the formula becomes

=IF(G10=1,INDEX('Urgent Express 1 day'!B4:H153,MATCH(G8,'Urgent Express 1 day'!A4:A153,0),MATCH(G6,'Urgent Express 1 day'!B3:H3,0)))

3. ## Re: Match/Index (Excel 2000)

Hans,

As always, A BIG THANK YOU!

Roberta

#### Posting Permissions

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