find value meeting certain criteria in a range and return that value

[bardobhb]

Hello, and thanks for taking time to help.

This is student testing data. Every column is a different question with a different correct answer, which is marked with a + in the cell. I need to find the correct answer from the range and return it in a different cell at the top of the column. The trickiness for me is that I never know where the correct answer will be found in the range, otherwise I could keep the simple if formula that is in there now.

I'd prefer to do it with a formula I think, as this is part of a template that many different people will use, who all have different data.

Hopefully the attached image or file helps to see what I mean.

sample.jpg

find and return sample.xlsx

[XOR LX]

Hi,

Perhaps something like:

=LOOKUP(101,SEARCH("*+*",B5:B15),B5:B15)

Regards

[bardobhb]

Thanks! That worked perfectly. I knew there had to be something relatively simple, and while I've used all the other main lookup functions, somehow I didn't even know there was a lookup.

Where does the 101 syntax come from? Perhaps there's a link to where you got that from?

[XOR LX]

You're welcome.

Actually, 101 is just an arbitrary number of my choosing. Technically, if you had entries in that column of length greater than this value, the function could fail, though I just reasoned that that would never be the case with grades going in there!

Regards

[bardobhb]

Got it. Thanks again!