Formula search range for a value where the result is the data from the cell next to it

[JBurtis]

Hey folks,

I'm stumped on this one. I feel like this should be simpler and I must be over complicating it.

I have 2 columns with data in them: column A and B.

Users enter a number into cell D2 and if that matches the number in column B, the output is a Yes/No in cell D7. No problem got that formula working. Easy peasy.

The trick is if the answer is Yes then I need a formula for cell D10 where the output is a single answer that does the same thing but instead of yes/no the result is the text from the cell in column A next to found number in column B. For example if D2 equals B22 in the B column range, then A22 is the output. It's the "cell next to" that I'm having trouble articulating.

[#2 · Accepted solution]

@JBurtisTo do this, you don't really need the intermediate Yes/No formula in D7, but perhaps you want it for other reasons. Let's say the numbers are in B2:B30 and the texts in A2:A30. The basic formula in D10 would then be:

=IFERROR(INDEX(A2:A30,MATCH(D2,B2:B30,0),1),"")

See if this gives the result you expect. 

[#3]

that works thanks! It's weird I know but apparently the team likes to see the answers yes, it's there and it's located here, in two separate neat output boxes. Thanks for your help!