vlookup solution?

[stilgar]

I have a excel sheet with all our product codes, product descriptions, and trade prices in it and i want to create a second sheet top act as a search sheet for the products.

The data on sheet 1 is on colums a, b, c, - a = codes, b = descriptions, c = price's

on sheet 2 i want a cell to search the codes and another cell to search the description.

then depending on what product is found the price is added automaticly to a third cell.

any idea's on the best way to do this?

 

[snoopyhr]

Does this help ?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
18	Sheet 1				Sheet 2
19	Codes	Description	Prices		Code	Code 3
20	Code 1	DESC -1	190		Description	DESC -3
21	Code 2	DESC -2	149		Price	135
22	Code 3	DESC -3	135
23	Code 4	DESC -4	181		Convert you date into a List, create an name range for codes
24	Code 5	DESC -5	123		in this case it is named CODES
25	Code 6	DESC -6	157		And adust the sheet ref, as needed.
26	Code 7	DESC -7	133
27	Code 8	DESC -8	189
28	Code 9	DESC -9	104
Sheet11

#VALUE!

 

[stilgar]

thats really good mate! nice work but i was hoping to have it so i could type in part of the code and it would suggest possiable matches.

also have it so you could type in a description or a code

 

[snoopyhr]

I am curious to look at your RAW data , do you mean for 1 description there are multiple codes?

 

[stilgar]

nahh each code has it's own description and it's own price.

eg:

FFBAF4PTD FFB 4P 4D ALUMINIUM FLUSH $768.87
FFBAF4PTDS60 FFB 4P 4D SHALLOW ALUM, FLUSH $768.87
FFBAR2PTD FFB 2P 2D ALUMINIUM RECESSED $750.36
FFBAR4PTDS60 FFB 4P 4D SHALLOW ALUM. RECES $803.82
FFBCTAR8PTD FFBCT 8P 8D ALUMINIUM RECESSED $1,036.94

there is 2195 rows of this data.

what i would like is a cell or box where you can search by the description or by the code.

so i could type in "ffbc" and it would suggest "ffbctar8ptd" or i could type in the description "8p 8d" and it would suggest "FFBCT 8P 8D ALUMINIUM RECESSED"

once one of them is selected then excel will fill in the corresponding code or description and price.

 

[snoopyhr]

Take a look at this, it might help you to get it started,
Follow the instruction and create two combo box, then implement what I posted earlier to it.
if you have any difficulty to do it, PM me with your e-mail I can then send you the sheet I worked on. Hope it works for you

the link for the video as how to create combo box is:
http://www.youtube.com/watch?v=J_ORTUwmhuo