One cell multiple outcomes

Copper Contributor

I am new to using excel so I apologize for what may seem to be a obvious answer to a simple questions. 

 

I have at the moment a drop down to select values A-G in cell A2. I want A3 to produce a predetermined value based on what was selected in cell A2. Currently I am able to make A3=U12 if A is selected in A2. But I want to include B=U2, C=U3, D=U4 etc. 

What formula should I use to produce these results? I am looking elsewhere on the web and not wrapping my head around on what I should be using. 

Again I apologize for the simplicity of my question. Thank you. 

3 Replies

@Tokon32 

I'd create a lookup list. In the screenshot below, it is in H1:I7, but it could be anywhere, for example on another sheet.

You can then use H1:H7 as source for the data validation dropdown in A2, and use a VLOOKUP or XLOOKUP formula in A3.

S1542.png

Formula:

=IFERROR(VLOOKUP(A2,$H$1:$I$7,2,FALSE),"")

Thank you for your response. If you have time and the desire to do so do you mind explaing this formula a little for me? Like I see the IFERROR than you go into the rest of the formula. The number 2 and the FALSE especially I dont know what they represent.

Thank you either way. Again only reply back if you have time.

@Tokon32 

VLOOKUP(A2,$H$1:$I$7,2,FALSE) searches for the value of A2 in the first column of $H$1:$I$7, i.e. in H1:H7.

The 4th argument FALSE instructs Excel to look for an exact match, not the default approximate match.

If the value is found, VLOOKUP returns the corresponding value from column #2 in $H$1:$I$7, i.e. I1:I7.

But if the value is not found, VLOOKUP returns the error value #N/A ("not available").

 

See Excel VLOOKUP Function for more info.

 

We wrap the entire VLOOKUP expression in IFERROR to replace that error #N/A (if it occurs) with the empty string "".

 

If you have Microsoft 365 or Office 2021, you can use

=XLOOKUP(A2,$H$1:$H$7,$I$1:$I$7,"")

instead. The result will be the same.