Novice list question

[Tomas23]

Hello,

Apologies about this novice question. I am currently stuck with the list function in excel. I have a database that takes a base item, adds a number of additions using drop down lists, and should give an updated cost. I have made the list function easily enough but I can't work out how to compare the result from the dropdown list to its value from a table in order to obtain a cost.

Sorry if this isn't making much sense, I will try and provide an example:

My data is on a separate workbook sheet and is in a table in the following format:

Red 1
Blue 3
Purple 14

I have a dropdown list where I can select the colour, and I would like to know how to match that selection to the value in the above table. So the end result would be that I could choose red, and the function would workout that red costs 1.

Thanks for looking,

Tomas.

 

[Aladin Akyurek]

Not enough information for cooking up an exact formula, but try...

=INDEX([WB.xls]Sheet1!$B$2:$B$4,MATCH(X2,[WB.xls]Sheet1!$A$2:$A$4,0))

 

[Mala]

Supplementary info for Tomas23:

[WB.xls]Sheet1!$A$2:$A$4 contains Red, Blue, Purple

[WB.xls]Sheet1!$B$2:$B$4 contains 1, 3, 14

X2 (on the sheet where the formula is located) contains the value (from Red, Blue, Purple) as obtained from the list box.

 

[Tomas23]

Thank you for your help so far, but I am struggling to make it work. I have followed the guide about posting examples, so hopefully this will work:

Book2
	A	B	C	D	E
1	Item	BaseCost	Colour	TotalCost
2	1	40	Blue
3
4
5
6
7
8
9
Sheet1

Where it says 'Blue' that is the drop down list that has the 3 colour options. The table with the colours and numerical values is on sheet 2 in cells A1:B3. The cells A1:A3 are named Colour and are where the list source is.

If it helps I am using Office 97 on windows XP.

Thanks again.

 

[Tomas23]

Couldn't edit the post above, however I have now got it to work, I was just being thick!

Thanks again,

Tomas.