Last cell/column in a row with a paticular data

[akshadd]

Hi All,

I have dynamnic no of columns for a row .
Except a first few columns for that row ,all successive columns will have data as either PASS or FAIL(see eg below). is there any excel function which can tell me which is the last column/cell with either PASS or FAIL.

e.g :
COL1 COL2 COL3 COL4 COL5 COL6
ABC 123 PASS FAIL PASS FAIL

Thanks
Akshay

 

[jasonb75]

Welcome to the board!

Is this what you're looking for?

Excel Workbook
	A	B	C	D	E	F	G	H
1	ABC	123	PASS	FAIL	PASS	FAIL		Col 5
2	ABC	124	PASS	FAIL	FAIL	PASS		Col 6
3	ABC	125	PASS	FAIL	PASS	FAIL		Col 6
4	ABC	126	PASS	FAIL	FAIL	PASS		Col 5
Sheet

 

[akshadd]

Hi Jason,

Thanks a ton for ur quick reply.
Well actually i am looking for the last column with data (PASS OR FAIL) , i.e in your example COL6 is the expected answer. I wanted to know at which column number the dynamic columns have ended .

Actually i will be having dynamic columns for a row with PASS or FAIL as its only data, i require the last column , that column no. will be same for all other rows .

 

[jasonb75]

If I understand what you're asking for correctly, try

="Col "&MAX(IF(((C1:F4="PASS")+(C1:F4="Fail"))>0,COLUMN(C1:F4),0))

I think that's what you need.