Forum Discussion
One cell multiple outcomes
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
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.
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.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.
