Find value in array then display that value

Hello!

I have an array of 8 columns and 50+ rows. Each row has one value that falls somewhere in the 8 columns. The rest of the columns are empty. I need a function to search the row for the value and then display that value in one cell.

Capture.PNG

Please let me know if you can help.

Thank you,
Jesse

If you are using 365 with the TOCOL function, how about

Formula:

=TOCOL(A3:H50,1)

An other possibility with XLOOKUP (available from Excel 2016)

Please try in I3 and copy down:

Formula:

=XLOOKUP(TRUE,""<>A3:H3,A3:H3,A3:H3)

Xlookup is not available in Excel 2016 unless you have a subscription, probably.
=lookup(2,1/(a3:h3<>""),a3:h3)

Originally Posted by T.I.

Xlookup is not available in Excel 2016

According Microsoft is XLOOKUP available in Excel 2016. See https://support.microsoft.com/en-us/...9-88eae8bf5929
[Click on more!]

Oh yes, the rest of the columns are empty.

Then I have 1 more solution for Excel 365/2021 users:

Formula:

=TEXTJOIN(,,A3:H3)

Originally Posted by HansDouwe

According Microsoft is XLOOKUP available in Excel 2016.

Hmm.. Notification from the link you've provided clearly states that it doesn't))

Note: XLOOKUP is not available in Excel 2016 and Excel 2019,

.
Besides, I'm using office 2013 and 2016 PP...

The information on Microsoft's site confuses me. Because above it can be read (if you clicked on More) that the function is available.
Attachment 795281

No matter how many times I click More or Less on Microsoft site, the function, alas, doesn't want to appear in a formula bar.
I suppose it might be available for those who have a subscription, but not sure...

Or try

=LOOKUP("zzzzz",A3:H3)

Yeah, XLOOKUP didn't come out until 365. It is confusing though. I wonder if you are on 2019 (or 2016) that it will *use* the formula if you are working with a file that was CREATED in 365, but I don't think you can write the formula in 2019 or 2016.

Yes, old offices allow to see the output from new functions at least until you try to change it, and at the same time warning you with _xlfn. prefix in front of a formula that it was created in a newer office.