If Col A contains the array and column B contains the lookup item, is there a way for column C to show the column A row that had the match? Or is there a way to have this happen in another Column
Thanks, MATCH is what I needed.
[Edit 2021-06-07 about 09:40 GMT]
Sorry my mention of MATCH() in the context below creates a definitely wrong statement. As opposed to the LOOKUP() functions it actualy returns a position (the 1-based index starting with the search range). I must have been fallen asleep when I posted the wrong thing.
Though the OriginalQuestioner may actually have found MATCH() as the means he looked for, he should credit it exclusively to @anon73440385 . My explanation was wong.
My example stressing the aspects of 2D-matching and probable multi-match findings is valid, however.
[/Edit]
I’m not quite sure what actually was asked.
Column AND row? How “the array” in column A only then?
Row only?
What if there are (can be) multiple matches in the searched range?
Anyway. MATCH and The variant lookup functions don’t return a position, but the result from a found cell.
In addition MATCH (and all the lookup variants) only can search either one column or one row.
If you want to get positions (and probably for matches from 2D ranges, you can use formulas working with explicit comparison and with COLUMN() and ROW() for the searched range under array evaluation (Ctrl+Shift+Enter when editing is finished).
You can even get lists of matching positions if there are many.
See the attached example for more details.
(The second solution there is based on a user function. If you don’t want to rely on “macros” simply ignore it.)
multiMatch2Dexample.ods
MATCH is what I needed, Thanks
After reading your answer/posting update I am at a loss in how to give exclusive credit to Opaque. I’m a noob, I tried clicking the up arrow adjacent to his reply but was prevented with a message stating the following: ‘>5 points required to upvote’
(Simply don’t worry. I also don’t know the details about the working of this site.)