data validation - reference cells other cells in Range

Question

So I have a data-validation sheet which I'm using as a drop down box. The data validation has a visible field and 2 fields which need to be referenced (and x and y value)

So the range is this:

answer --- x --- y  
|          |     | 
YES ------ 3 --- 0
|          |     |
NO ------- 5 --- 1

How can I have the user select YES via a data-validation list and then reference 3 (the corresponding X value) on a different cell?

In MS Access, I would create a combobox and have Answer, x, y and have the visible columns be 1"; 0"; 0". Then to reference the value, I'd use combobox.column(1).

I'm not sure how to reference that in Excel.

Answer 1

You can refer to the validation cell as any other cell in your formulas. Therefore, you can do If() statements. For example, in your cell B1, you can just do a formula: =IF($A$1="Yes",3,IF($A$1="No",5,"")).

If you have lots of possibilities, you can set up a vlookup table, and just put a vlookup formula in B1, using A1 as the lookup value.