Return value determined by multiple criteria

[Nicole Jones]

Not sure if what I am asking is possible.

I am creating a register that will capture revision data using a single row for each document number. These documents are being uploaded into two different areas "Q" and "I" and I would like to include two "current status" columns in my register. I would like a formula in each of these columns to look at the row and only return the value if the location above corresponds to it.

In the image I have colour coded what values the 'status' columns should be returning based on the Rev value not being null and the location above containing a Q or I.

I really hope this is clear and that it is possble.

Thank you in advance for your assistance.

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G
1			Q&I	Q&I	Q	Q	I
2	Q Status	I Status
3	A	B	PLH		A		B
4	A	PLH		PLH		A
5	PLH	PLH		PLH
Sheet3

Cell Formulas
Range	Formula
A3:A5	A3	=TAKE(FILTER(C3:G3,ISNUMBER(SEARCH("Q",$C$1:$G$1))*(C3:G3<>"")),,-1)
B3:B5	B3	=TAKE(FILTER(C3:G3,ISNUMBER(SEARCH("I",$C$1:$G$1))*(C3:G3<>"")),,-1)

 

[Nicole Jones]

Try:

Book1
	A	B	C	D	E	F	G
1			Q&I	Q&I	Q	Q	I
2	Q Status	I Status
3	A	B	PLH		A		B
4	A	PLH		PLH		A
5	PLH	PLH		PLH
Sheet3

Cell Formulas
Range	Formula
A3:A5	A3	=TAKE(FILTER(C3:G3,ISNUMBER(SEARCH("Q",$C$1:$G$1))*(C3:G3<>"")),,-1)
B3:B5	B3	=TAKE(FILTER(C3:G3,ISNUMBER(SEARCH("I",$C$1:$G$1))*(C3:G3<>"")),,-1)

This is amazing, thank you so much.

 

[smozgur]

This is amazing, thank you so much.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers.
No further action is required for this thread.