Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match/Index (Excel 2000)

    Hans,

    As always, A BIG THANK YOU!

    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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