Find First Occurrence Of A Value In A Range Of Cells And Return The Leftmost Column Value

I have a spreadsheet with customers' names down the leftmost column (Column A.

Each customer has 5 ticket numbers. There is a column for each of the 5 ticket numbers (Columns B through F). I want to look in the range (B - F) to find a specific ticket number and then return the name of the customer holding that ticket number.

Any ideas how I do this, please? (It's not VLookUp, I've tried that already and that does the reverse of what I want to do.)

Screenshot2.jpg

Interesting problem, i tried all types of formulas to make this work, but couldn't succeed, which prompted me to look further.

Turns out that this is not a trivial matter to solve with standard excel functions, based on further research i did find a solution using a UDF (user defined function), this solution was found at the following link

HTML Code: 

The attached workbook has the UDF included and it works great.

Originally Posted by bebo021999

I've tested and it worked:

=INDEX(A:A,MAX(INDEX((B2:F15=H7)*ROW(A2:A15),)))

Yes, this solution works just fine, it actually a nice solution, I will most certainly learn from it, in most cases I would also prefer a non VBA solution.

Originally Posted by bebo021999

Try :

=INDEX(A:A,MAX(INDEX((B2:F15=88)*ROW($A$2:$A$15),)))

Thank you, bebo021999. That's a great solution and yes, indeed, it does work. That is, until you have more than one occurrence of the same number. In which case, it does not seem to find the first occurrence.

Originally Posted by jprlimey

Interesting problem, i tried all types of formulas to make this work, but couldn't succeed, which prompted me to look further.

Turns out that this is not a trivial matter to solve with standard excel functions, based on further research i did find a solution using a UDF (user defined function), this solution was found at the following link

HTML Code: 

The attached workbook has the UDF included and it works great.

Thanks, jprlimey. This does the trick perfectly! #ThumbsUp