LOOKUP formula - I think

Question

LOOKUP formula - I think

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)

Posted on May 8, 2013 10:04 AM

Reply

Answer 1

Best reply

Jerrold Green1

Level 7

30,211 points

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

Reply

Answer 2

Barry

Level 9

59,867 points

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:

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

Reply

Answer 3

May 8, 2013 11:09 AM in response to Jerrold Green1

Jerry,

Thank you so much for your help. I will begin experimenting with VLOOKUP function and see if I can figure it out.

Here is some more info about what I'm trying to accomplish and how it looks in the source document. Basically. I would like to be able to type in the product code onto the 'order form' page (for example 'B12') and have numbers automatically fill-in the info from cells C4:I4 of the source document (sheet 2) to the 'Order Form' page (sheet 1). I would like to keep the column spacing the same when it auto fills if that's possible as well. I'm fairly new and this is really my first attempt at anything beyond the very basic functions so I greatly appreciate your help!

Here is a screen shot of a portion of the source document:

Thanks again,

Scott

Reply

Answer 4

May 8, 2013 11:16 AM in response to Barry

Thank you Barry!

I believe what you're describing is very similar to (if not exactly) what I'm trying to do. I'm going to experiment with it and see if I can get it to work. I really appreciate your help!

Thank you again for your advice!

Scott

Reply

Answer 5

Barry

Level 9

59,867 points

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:

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

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

Regards,

Barry

Reply

Answer 6

May 8, 2013 1:53 PM in response to Barry

Barry,

I just want to say thank you! I've been messing around with it for a couple hours and have gotten it to work exactly as you described.

Thank you again for all your help!!

Scott

Reply