VLOOKUP / Auto Fill / IF?

L

Luke22

Hi

Im creating a table that always me to select certain cabinets, then in the
next cell select a certain width range that applies to the cabinet in the
first cell. Then have the 3rd cell auto fill with the correct price relating
to the cabinet and its size. At the moment i have a VLOOKUP set up with a
drop down tab that only allows me to select cabinets that ive specified, then
in the next cell select a certain size for that cabinet in the first cell. I
now want the 3rd cell to auto fill with the price of that cabinet in that
size.

Carcass Width Flat Pack Price
B1D 301-400 ???




I have set up a data page on a seperate work sheet that has all the
infomation i should need.

Carcass Width Flat Pack Price Carcasses
B1D 201-300 $36.34 B1D
B1D 301-400 $41.34 B2D
B1D 401-500 $46.33 B3D
B1D 501-600 $51.33 B2DC
B2D 501-600 $51.33 B3DC1
B2D 601-700 $56.33 B3DC2
B2D 701-800 $61.32 B3DC3
B2D 801-900 $66.32 BO
B2D 901-1000 $71.32 BOD
B2D 1001-1100 $76.31 B1DS
B2D 1101-1200 $81.31 B2DS
B3D 1000-1100 $80.33 D1D
B3D 1101-1200 $85.33 D2D
B3D 1201-1300 $90.32 D3D
B3D 1301-1400 $95.32 D3DU
B3D 1401-1500 $100.32 D4D
B3D 1501-1600 $105.31 D5D
B3D 1601-1700 $110.31 D1DB1
B3D 1701-1800 $115.31 D1DB2
and on and on it goes

Is it possible to auto fill this 3rd cell?

Note: I've been playing around with excel spread sheets for all of 1 week.
So my knowledge is extremely limited.

Thanks for your time!!
Luke
 
S

Sheeloo

Why not use VLOOKUP?

Insert Col C in your lookup sheet (Sheet2)
Enter in C2
=A2&B2 and copy down

Now where you have shown ??? enter (assuming it is row 2)
=VLOOKUP(A2&B2, Sheet2!C:D,2,FALSE) and copy down

You may put an IF around it to supress the error message when A2 and/or B2
are blank...
 
L

Luke22

Thank you!!

Where do i put the IF in the formula to hide the n/a when no data is entered?
 
S

Sheeloo

=IF(ISERROR(VLOOKUP(A2&B2, Sheet2!C:D,2,FALSE)),"",=VLOOKUP(A2&B2,
Sheet2!C:D,2,FALSE))

=IF(ISERROR(VLOOKUP(A2&B2, sheet2!C:D,2,FALSE)),"",VLOOKUP(A2&B2,
Sheet2!C:D,2,FALSE))


If you're using xl2007, you could use:
=IFERROR(VLOOKUP(A2&B2, Sheet2!C:D,2,FALSE),"")
 
L

Luke22

Thanks again :)

Sheeloo said:
=IF(ISERROR(VLOOKUP(A2&B2, Sheet2!C:D,2,FALSE)),"",=VLOOKUP(A2&B2,
Sheet2!C:D,2,FALSE))

=IF(ISERROR(VLOOKUP(A2&B2, sheet2!C:D,2,FALSE)),"",VLOOKUP(A2&B2,
Sheet2!C:D,2,FALSE))


If you're using xl2007, you could use:
=IFERROR(VLOOKUP(A2&B2, Sheet2!C:D,2,FALSE),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top