Skip navigation

LOOKUP formula - I think

315 Views 6 Replies Latest reply: May 8, 2013 1:53 PM by scottfrombennington RSS
scottfrombennington Calculating status...
Currently Being Moderated
May 8, 2013 10:04 AM

I would like to create a pricing program of sorts with numbers.

 

First, I want to create a source document listing all my product codes in the first column followed by four columns containing the list prices and relevant information.

 

Then I want to create a second spreadsheet that will look like an order form in which I can type in a product code (matching the source page identically) and have numbers automatically fill in the remaining columns from the source document onto the 'order form' page.  Is there a way to get numbers to recognize a product code and fill in the corresponding info from a source document into another sheet?

 

I've been playing around with the LOOKUP and HLOOKUP function, but can't seem to get it figured out.

 

Thanks!

iMac, OS X Mountain Lion (10.8.2)
  • Jerrold Green1 Level 7 Level 7 (28,160 points)
    Currently Being Moderated
    May 8, 2013 10:39 AM (in response to scottfrombennington)

    Scott,

     

    VLOOKUP would be just the thing for this situation. It's not the only way, but it would be perfectly fine. If you want some help writing the expressions, post a screen shot of your tables so we can give specific advice.

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 8, 2013 10:51 AM (in response to scottfrombennington)

    Hi Scott,

     

    LOOKUP will work. VLOOKUP may be a better choice, as you can specify an exact match. LOOKUP always accepts a 'close match', meaning 'The largest alue equal to or less than the search value.'

     

    Here's a sample:

    Screen Shot 2013-05-08 at 10.45.45 AM.png

     

    Data table contains enterd data; no formulas.

     

    Columns C, D and E of order contain formulas. Each is entered in row 2, and filed down the rest of the column.

     

    C2: =IFERROR(VLOOKUP($B,Data :: $A:$D,2,FALSE),"")

    D2: =IFERROR(VLOOKUP($B,Data :: $A:$D,4,FALSE),"")

     

    E2: =IF(LEN(D)>0,A2*D2,"")

     

    Functions used are described in the iWork Formulas and Functions User Guide. The guide is available for download via the Help menu in Numbers '09.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 8, 2013 11:25 AM (in response to scottfrombennington)

    Hi Scott,

     

    Spacing on the order form is set when you set up that form. If you want to match the spacing on the source document, then duplcate the source table, delete the data from the duplicate, and rpplace it with the formulas.

     

    If you are transferring data from consecutive columns, you can replace the 2 and 4 in my two VLOOKUP formulas with COLUMN()-n.  Adjust n to return the correct data from the first column to be transferred, then fill the formula right to pick up succeeding columns.

    Here's a revised sample with the "Notes" column added and the VLOOKUP formula revised:

    Screen Shot 2013-05-08 at 11.23.48 AM.png

     

    C2: =IFERROR(VLOOKUP($B,Data :: $A:$D,COLUMN()-1,FALSE),"")

    Fill right to E2. Fill down to end of table.

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.