Return one value based on the result of another

[slaughtontdc]

Hi

Say I have a dropdown list in cell A1 which has ITEM 1, ITEM 2, ITEM 3 for the users to choose from.

Say in B2 I want the content to be decided by what the user selects from the dropdown list in A1.

For example, if the user selects ITEM 1 from the A1 drop down list, I want B1 to say APPLE and if they select ITEM 2 I want B1 to say ORANGE etc

What is the correct statement to put in B2? I would have thought it would be something like:

IF (A1 = ITEM 1, B2 = APPLE) - excuse the simpleton approach!

Thanks

[ben_hensel]

Well, the Syntax for the IF would be more like,

Please Login or Register  to view this content.

1) If it's in B2, that's the location of the output--typing it into the field is incorrect.
2) Text strings need to be enclosed in double-quotes.
3) You cannot leave the false implicit. The paired double quotes, "", will pass an empty text string.

But, more towards the idea of the question,

This isn't a good place for an IF. A VLOOKUP is better. You'll need to build a table for it to use.

[slaughtontdc]

Thanks for the quick reply Ben. This sounds complicated!

I will look into the VLOOKUP command !

[Richard Buttrey]

Please note, and since this is your first post, I've edited your title, which should be meaningful - see forum rules.

You need to use a Vlookup() formula.

Put a two column table together with Items in 1 column and in the adjacent column the associated fruit name. e.g.

Item1 Apple
Item2 Orange

Let's assume this table is in say E1:F10

Then in B2 enter

=VLOOLUP(A1,$E$1:$F$10,2,False)

[slaughtontdc]

Thanks! Sorry I thought the title was clear for a beginner like I am!

Where do you create the tables as I obviously dont want the user to see the info behind the dropdown list or the VLOOKUP command.

On a seperate note, can you perform calculations across different sheets? ie is A1 in sheet 1 is a certain value then A1 in sheet 2 should have another set value
Thanks

[Richard Buttrey]

Hi,

As suggested put the table wherever you want. My example was E1:F10 (or however many rows you need for the table). If you don't want the user to see the table then just hide the columns. Similarly with the VLOOKUP. Either hide the column or put it on another sheet

Alternatively put the table on a second sheet and hide the sheet. In this case you'll need to include the sheet name in the formula. e.g.

=VLOOLUP(A1,'Sheet1'!$E$1:$F$10,2,False)

Similarly with the VLOOKUP. Either hide the column or put it on another sheet.

Re your 2nd point I don't understand what you're asking. You can use normal IF test structures to compare a cell on one sheet with a cell on another return a pre-defined value.

I'll need to see a worked example workbook to be able to advise further on this.