I need some type of match / find / look up, non-vba function. Cannot sort, or in any way manipulate the array of data.

I have Columns A - E with data. Column A contains unique values in each row. Rows in Columns B - E contain between 0 and 4 values from Column A (one value per cell). No value is repeated within Columns B - E.

I need to find a value in Columns B - E and return the corresponding value from Column A of that row. I must use Columns B - E for the search and need a 1 cell solution, per search function.

A     B     C     D     E
1     2
2     3     5
3     4     7     8     9     
4     
5     6     
6
7  
8
9

4/26/2017 12:06:05 PM

Got it

={INDEX(A1:A9,MAX(IF(B1:E9=[i]Searched Value[\i],ROW(B1:E9)-ROW(A1)+1)))}

4/26/2017 12:35:49 PM

would vlookup not work for this?

4/26/2017 1:45:14 PM

no

4/26/2017 3:24:12 PM

Quote :

"-ROW(A1)+1"

What is the point of this? ROW(A1) will always return 1, regardless of your input cell. you're subtracting and adding one unnecessarily.

4/26/2017 4:17:40 PM

^ This is an array formula. The "A1" is behaving differently because of that. It effectively refers to the first column on whatever row the function is evaluating.

[Edited on April 27, 2017 at 12:48 AM. Reason : ]

4/27/2017 12:41:47 AM

it's not though. I tested the code before writing that, and it works fine without it.

[Edited on April 27, 2017 at 1:08 PM. Reason : nevermind. it's needed if the cells aren't actually located in B1-E9. not an array issue though]

4/27/2017 1:05:31 PM