Get rightmost non date value in a row

[SingularitysPoint]

We have numerous sheets of thickness readings. They take the form of something similar to:

ITEM	POS	BASE THK	BASE DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE
001	A	5.1	11/06/15	4.9	13/06/23	536	25/09/19	5.1	11/06/15
002	B	7.6	25/09/19	8.25	13/06/23	7.6	25/09/19	N	11/06/15
003	C	5.2	11/06/15	5.1	13/06/23	5.3	25/09/19	5.2	11/06/15

The number of columns after the Item and Position can vary, depending on how many readings we have but, if there isn't a reading one year, the survey date is still entered and an 'N' (for No Reading) is entered.

What I need to get (in the BASETHK and BASEDATE cells) is the rightmost thickness reading for one, and the respective date for the other. The colouring above is purely to show where the results in the BASE columns are coming from.

Is this possible? I've Google lots, and tried LOOKUP, INDEX/MATCH and so on, but without real success.

 

[Peter_SSs]

Apology accepted. Just remember to provide any cross-post links on both/all forums in the future.
It is also worth considering whether posting on multiple forums actually makes things faster. There are numerous helpers in all forums that will just pass over your thread if it is cross-posted because they do not want to bother trying to monitor multiple forums to see if an answer &/or additional information has been provided elsewhere. So you may have two forums in play, but less potential helpers in each one.

 

[Peter_SSs]

Cheers.

 

[Legacy 143009]

For the setup below:

C2:

=INDEX($E2:$J2,IFERROR(MATCH("N",$E2:$J2,0),COUNTA($E2:$J2)+1)-(COLUMN()-1))

 

[Peter_SSs]

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’)

 

[SingularitysPoint]

Thank you. T

For the setup below:
View attachment 93533
C2:

=INDEX($E2:$J2,IFERROR(MATCH("N",$E2:$J2,0),COUNTA($E2:$J2)+1)-(COLUMN()-1))

Thank you so much. That's excellent and works great for the thicknesses. How do I get it to show the respective date (which is the one to the right or the selected figure)?

I've notced, if there's more than one N, or an N in the middle of the result range, it doesn't quite work. I'm uploading a better example.

BASELINE FORMULA TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	POS	BASE THK	BASE DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE
2	001	A	5.6	18/09/2007	5.1	13/03/2023	5.4	25/09/2019	5.6	11/06/2015	5.5	22/02/2011	5.6	18/09/2007
3	002	B	7.9	18/09/2007	8.2	13/03/2023	7.6	25/09/2019	7.7	11/06/2015	8	22/02/2011	7.9	18/09/2007
4	003	C	7.8	22/02/2011	7.6	13/03/2023	7.7	25/09/2019	7.7	11/06/2015	7.8	22/02/2011	N	18/09/2007
5	004	D	7.5	18/09/2007	7.3	13/03/2023	7.1	25/09/2019	7.2	11/06/2015	7.4	22/02/2011	7.5	18/09/2007
6	005	E	4.3	18/09/2007	3.9	13/03/2023	N	25/09/2019	4.2	11/06/2015	4.2	22/02/2011	4.3	18/09/2007
7	006	F	3.5	18/09/2007	3.7	13/03/2023	3.2	25/09/2019	3.4	11/06/2015	3.3	22/02/2011	3.5	18/09/2007
8	007	G	3.7	22/02/2011	3.8	13/03/2023	3.1	25/09/2019	N	11/06/2015	3.7	22/02/2011	N	18/09/2007
9	008	H	8.4	11/06/2015	8.1	13/03/2023	8.2	25/09/2019	8.4	11/06/2015	N	22/02/2011	N	18/09/2007
10	009	I	8.5	18/09/2007	8.1	13/03/2023	8.2	25/09/2019	8.2	11/06/2015	8.4	22/02/2011	8.5	18/09/2007
11	010	J	5.8	18/09/2007	5.4	13/03/2023	5.3	25/09/2019	5.4	11/06/2015	5.7	22/02/2011	5.8	18/09/2007
Sheet1

Cell Formulas
Range	Formula
C2:D3,C5:D7,C10:D11	C2	=M2
C4:D4,C8:D8	C4	=K4
C9:D9	C9	=I9

 

[SingularitysPoint]

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’)

Thanks. I've now done that.

 

[Peter_SSs]

Thanks. I've now done that.

Cheers. Thanks for that.

Try these

23 06 14.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N
1	BASE THK	BASE DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE
2	5.6	18/09/2007	5.1	13/03/2023	5.4	25/09/2019	5.6	11/06/2015	5.5	22/02/2011	5.6	18/09/2007
3	7.9	18/09/2007	8.2	13/03/2023	7.6	25/09/2019	7.7	11/06/2015	8	22/02/2011	7.9	18/09/2007
4	7.8	22/02/2011	7.6	13/03/2023	7.7	25/09/2019	7.7	11/06/2015	7.8	22/02/2011	N	18/09/2007
5	7.5	18/09/2007	7.3	13/03/2023	7.1	25/09/2019	7.2	11/06/2015	7.4	22/02/2011	7.5	18/09/2007
6	4.3	18/09/2007	3.9	13/03/2023	N	25/09/2019	4.2	11/06/2015	4.2	22/02/2011	4.3	18/09/2007
7	3.5	18/09/2007	3.7	13/03/2023	3.2	25/09/2019	3.4	11/06/2015	3.3	22/02/2011	3.5	18/09/2007
8	3.7	22/02/2011	3.8	13/03/2023	3.1	25/09/2019	N	11/06/2015	3.7	22/02/2011	N	18/09/2007
9	8.4	11/06/2015	8.1	13/03/2023	8.2	25/09/2019	8.4	11/06/2015	N	22/02/2011	N	18/09/2007
10	8.5	18/09/2007	8.1	13/03/2023	8.2	25/09/2019	8.2	11/06/2015	8.4	22/02/2011	8.5	18/09/2007
11	5.8	18/09/2007	5.4	13/03/2023	5.3	25/09/2019	5.4	11/06/2015	5.7	22/02/2011	5.8	18/09/2007
Sheet5

Cell Formulas
Range	Formula
C2:C11	C2	=LET(f,FILTER(E2:N2,(E$1:N$1="THICKNESS")*(E2:N2<>"N")),INDEX(f,COUNT(f)))
D2:D11	D2	=XLOOKUP(C2,E2:M2,F2:N2,,,-1)

 

[Peter_SSs]

Here is another option with a shorter formula for thickness.

23 06 14.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N
1	BASE THK	BASE DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE
2	5.6	18/09/2007	5.1	13/03/2023	5.4	25/09/2019	5.6	11/06/2015	5.5	22/02/2011	5.6	18/09/2007
3	7.9	18/09/2007	8.2	13/03/2023	7.6	25/09/2019	7.7	11/06/2015	8	22/02/2011	7.9	18/09/2007
4	7.8	22/02/2011	7.6	13/03/2023	7.7	25/09/2019	7.7	11/06/2015	7.8	22/02/2011	N	18/09/2007
5	7.5	18/09/2007	7.3	13/03/2023	7.1	25/09/2019	7.2	11/06/2015	7.4	22/02/2011	7.5	18/09/2007
6	4.3	18/09/2007	3.9	13/03/2023	N	25/09/2019	4.2	11/06/2015	4.2	22/02/2011	4.3	18/09/2007
7	3.5	18/09/2007	3.7	13/03/2023	3.2	25/09/2019	3.4	11/06/2015	3.3	22/02/2011	3.5	18/09/2007
8	3.7	22/02/2011	3.8	13/03/2023	3.1	25/09/2019	N	11/06/2015	3.7	22/02/2011	N	18/09/2007
9	8.4	11/06/2015	8.1	13/03/2023	8.2	25/09/2019	8.4	11/06/2015	N	22/02/2011	N	18/09/2007
10	8.5	18/09/2007	8.1	13/03/2023	8.2	25/09/2019	8.2	11/06/2015	8.4	22/02/2011	8.5	18/09/2007
11	5.8	18/09/2007	5.4	13/03/2023	5.3	25/09/2019	5.4	11/06/2015	5.7	22/02/2011	5.8	18/09/2007
Sheet5

Cell Formulas
Range	Formula
C2:C11	C2	=LOOKUP(9^9,FILTER(E2:N2,E$1:N$1="THICKNESS"))
D2:D11	D2	=XLOOKUP(C2,E2:M2,F2:N2,,,-1)

 

[SingularitysPoint]

Here is another option with a shorter formula for thickness.

23 06 14.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N
1	BASE THK	BASE DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE	THICKNESS	DATE
2	5.6	18/09/2007	5.1	13/03/2023	5.4	25/09/2019	5.6	11/06/2015	5.5	22/02/2011	5.6	18/09/2007
3	7.9	18/09/2007	8.2	13/03/2023	7.6	25/09/2019	7.7	11/06/2015	8	22/02/2011	7.9	18/09/2007
4	7.8	22/02/2011	7.6	13/03/2023	7.7	25/09/2019	7.7	11/06/2015	7.8	22/02/2011	N	18/09/2007
5	7.5	18/09/2007	7.3	13/03/2023	7.1	25/09/2019	7.2	11/06/2015	7.4	22/02/2011	7.5	18/09/2007
6	4.3	18/09/2007	3.9	13/03/2023	N	25/09/2019	4.2	11/06/2015	4.2	22/02/2011	4.3	18/09/2007
7	3.5	18/09/2007	3.7	13/03/2023	3.2	25/09/2019	3.4	11/06/2015	3.3	22/02/2011	3.5	18/09/2007
8	3.7	22/02/2011	3.8	13/03/2023	3.1	25/09/2019	N	11/06/2015	3.7	22/02/2011	N	18/09/2007
9	8.4	11/06/2015	8.1	13/03/2023	8.2	25/09/2019	8.4	11/06/2015	N	22/02/2011	N	18/09/2007
10	8.5	18/09/2007	8.1	13/03/2023	8.2	25/09/2019	8.2	11/06/2015	8.4	22/02/2011	8.5	18/09/2007
11	5.8	18/09/2007	5.4	13/03/2023	5.3	25/09/2019	5.4	11/06/2015	5.7	22/02/2011	5.8	18/09/2007
Sheet5

Cell Formulas
Range	Formula
C2:C11	C2	=LOOKUP(9^9,FILTER(E2:N2,E$1:N$1="THICKNESS"))
D2:D11	D2	=XLOOKUP(C2,E2:M2,F2:N2,,,-1)

That works perfectly! Thank you SO much Peter.