Select Value +Return Value Based on Data Location.

[Roj47]

Hi, and do feel a little daft asking this as it seems so easy (?).

I have data (numeric e.g. 47 and test e.g. DT) which are entered into a handful of cells in a row.

There are 10 columns (D to M) with the current most recent position as you get to M.

Is there a function that I can enter into Column N to return the entered value closest to Column N i.e.

If Column E has 47, I would like Column N to return 47; If I enter DT into column L, then the value in Column N returns DT.... Likewise if I remove DT from column L the value reverts back to 47.

Searching keeps helping me get the closest value in a different way.

Thank you.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=LOOKUP(2,1/(D2:M2<>""),D2:M2)

 

[Roj47]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=LOOKUP(2,1/(D2:M2<>""),D2:M2)

I have checked the 365 option and my platform - Thank you for pointing out this had not happened.

The solution was perfect, thank you on this also.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[EFANYoutube]

This is what I got

=IFERROR(IF(IFERROR(XMATCH(47,D1:M1,0,-1),0)>IFERROR(XMATCH("DT",D1:M1,0,-1),0),INDEX(D1:M1,1,XMATCH(47,D1:M1,0,-1)),INDEX(D1:M1,1,XMATCH("DT",D1:M1,0,-1))),"None")

Fluffs is better, I misread your post in thinking those were the only 2 things you wanted to test for