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