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.

 

[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.

 

[Peter_SSs]

You're welcome, glad we could help. Thanks for the follow-up.

 

[Fluff]

For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Get rightmost non-date or text value in a row [SOLVED]
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.