Looking up information and returning a value

[Guest]

I have an Excel worksheet where I'm trying to display a value in a field
based on the state selected in another field.

For example if the state is TX, the value is 100, if the State is FL, the
value is 120 etc.

I have the states listed on a worksheet in column A and the corresponding
values in column B.

Then I set up the second worksheet, same workbook with my other information.
On this worksheet I want to select a state from a drop down (which I've
done) and based on that selection populate the next cell (i.e. E1) with the
appropriate value based on my look up table.

How do I do this?

Your help is much appreciated.

 

[Roger Govier]

Hi

On sheet2, in cell E1
=IF($D1="","",VLOOKUP($D1,Sheet1!$A$1:$B$100,2,0))
Copy down as required

 

[Guest]

Hi Camp,

your drop down should refers to one cell (lets say H1), where excel will
return 1, 2, 3, etc depending each state you choose.

create a new column in your tabe to have:

A B C
1 1 FL 120
2 2 NW 80
3 3 TX 100

have notice that the new A column has an auxiliar "ID"

on E1 use =vlookup(H1,WS1!a2:c51,3,false)

I consider WS1 the worksheet where you have you table

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Camp7640" escreveu:

 

[Guest]

Thanks! Worked like a charm

Hi Camp,

your drop down should refers to one cell (lets say H1), where excel will
return 1, 2, 3, etc depending each state you choose.

create a new column in your tabe to have:

A B C
1 1 FL 120
2 2 NW 80
3 3 TX 100

have notice that the new A column has an auxiliar "ID"

on E1 use =vlookup(H1,WS1!a2:c51,3,false)

I consider WS1 the worksheet where you have you table

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Camp7640" escreveu:

 

[Guest]

glad to help, thanks for the feedback

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Camp7640" escreveu: