locked
Can anyone help with this formula??? RRS feed

  • Question

  • ·          

    I have a workbook with two sheets.  Sheet 1 is blank in column A (Price) and Item# in Column B.  Sheet 2 has Item #s in Column A and pricing in Column D.  I'm trying to take an item # on sheet one (b2), find the corresponding item # on sheet two (somewhere in column a), go over to column d on same row and retrieve corresponding price and bring it to sheet 1, column A, next to item # (see example below):

    Sheet 1    A            B                             Sheet 2     A                                D

                Price        Item#                                       Item #                      Price

                (blank)     1122                                         2233                           $10

                (blank)     2233                                         5566                           $5

                (blank)     3344                                          1122                           $2

     

    I want $2 in A2, $10 in A3, etc. 

    Friday, May 28, 2010 4:28 AM

Answers

  • kamikimai wrote,
    Good morning. I just tried this formula and I get #N/A...any suggestions?
    Are you using XL2003? Try specifying a range that is not a whole column.

    =INDEX(Sheet2!$D$1:$D$1000,MATCH(B2,Sheet2!$A$1:$A$1000,0))

    I am assuming that cell B2 contains an item you wish to search for like
    1122.


    Ed Ferrero www.edferrero.com
    • Marked as answer by Sally Tang Tuesday, June 1, 2010 8:02 AM
    Monday, May 31, 2010 12:11 AM
    Answerer

All replies

  • I'm off to bed, but if anyone can figure this out for me I would be forever grateful.  I know it's probably not that difficult for all the Excel gurus out there, but I'm not familiar enough with formulas to figure it out.  I'll check back in the a.m. to see if anyone's got the solution.  Thanks so much!!!
    Friday, May 28, 2010 4:45 AM
  • In A2 enter =INDEX(Sheet2!D:D,MATCH(B2,Sheet2!A:A,0))

    Copy down.

    Ed Ferrero www.edferrero.com
    Friday, May 28, 2010 7:34 AM
    Answerer
  • Good morning.  I just tried this formula and I get #N/A...any suggestions?
    Friday, May 28, 2010 10:44 AM
  • kamikimai wrote,
    Good morning. I just tried this formula and I get #N/A...any suggestions?
    Are you using XL2003? Try specifying a range that is not a whole column.

    =INDEX(Sheet2!$D$1:$D$1000,MATCH(B2,Sheet2!$A$1:$A$1000,0))

    I am assuming that cell B2 contains an item you wish to search for like
    1122.


    Ed Ferrero www.edferrero.com
    • Marked as answer by Sally Tang Tuesday, June 1, 2010 8:02 AM
    Monday, May 31, 2010 12:11 AM
    Answerer