If Value in one cell = the value in another, then ...

[ntdtyler]

Hi guys i am a very basic excel user and might only take u guys a min to help me out on this one, but any help will be appreciated.

Lets say i have in A1, A2, A3

TV
BLURAY
VCR

and then prices for each in B1, B2, B3

£500
£200
£100

Now i create a dropdown list box in C1 which lets me select TV, BLURAY, VCR.

i can do that bit fine, but now i want D1 to automatically fill the box with the value of the 'TV' (which is in cell B1 and is £500)

im just stuck on the forumla, which in simple terms i guess is


IF i select TV in the list box then D1 will copy the values of B1 (which is the price of the item) but if i select BLURAY in the list box then D1 will copy over the values of B2, and so forth.

Thanks Guys

[davesexcel]

Give This a go
=IF(ISERROR(VLOOKUP(C1,$A$1:$B$3,2,0)),"",(VLOOKUP(C1,A1:B3,2,0)))

[Ron Coderre]

I'm guessing that when you say "listbox" you mean a data validation dropdown list.
If that's true, try this:

D1: =VLOOKUP(C1,A1:B3,2.0)

If you're unfamiliar with Data Validation....
• Select C1
• Data.Validation
• Allow: List
• Data: select the A1:A3 range
• Click: OK

Does that help?

[ntdtyler]

thanks dave, that was excellent...........i was attempting by myself for ages without any luck.

your a life saver

[ntdtyler]

thanks to you too ron, now i have a choice of two! lol

[ntdtyler]

ok i have one more question the formula both of u have given me are working, but on this one:

=IF(ISERROR(VLOOKUP(C1,$A$1:$B$3,2,0)),"",(VLOOKUP(C1,A1:B3,2,0)))

my table array cells are A4-6 AND G4-6 Only, but when i try typing in

(VLOOKUP(C1,$A$4:$G$6,2,0) it obviously highlights everything including B,C,D,E,F etc. is there any way i can just only get A and G in the array?

Thanks again guys

[spreadcore]

(VLOOKUP(C1,$A$4:$G$6,2,0) it obviously highlights everything including B,C,D,E,F etc. is there any way i can just only get A and G in the array?

With the item in "A" and the price in "G", all you have to do is change the column index number in vlookup-(change the 2 to a 7). The column index number tells which column your price is in. The first column in the array is 1 the next 2 etc etc. so A is 1, G is 7

Change this
=VLOOKUP(C1;A4:G6;2;0)
to this
=VLOOKUP(C1;A4:G6;7;0)

[Ron Coderre]

Spreadcore helped with the VLOOKUP part...

Here's something else to consider:
Since C1's value is controlled by a DV dropdown list,
it can only contain a valid entry or a blank.

Consequently, I believe this would work for you:
D1: =IF(C1="","",VLOOKUP(C1,A4:G6,7,0)))