Find and Lookup, return values based on priority in list-1 sheet

[mmr1]

Hi,

Here I have a list-1 in that list need to be extract the required items only in list-2,One is high level and Second one is blank cells where yet to be decide work location priority level.

and as per Priority level there should be return further values which are contains in a same row.

I have needed conditional formatting to the list-1 so then quickly verify and highlight that the formulas in Column G to J are correct.


Thanks for the help,

Find and Lookup, return values based on Priority in listed sheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1		List-1					List-2
2		Work Location	Work Notification Ref	Estimated Quantity of rectification in Meters	Priority		Priority	Work Location	Work Notification Ref	Estimated Quantity of rectification in Meters
3		Area-1	LT-02153	100	High level
4		Hotel Parking Area	LT-02154	15	Medium level
5		Area-1	LT-02153	214
6		Area-4	LT-02156	152	Normal level
7		Car Parking Area	LT-02157	365
8		Area-6	LT-02158	145	Normal level
9		Car Parking Area	LT-02159	142	Normal level
10		Area-8	LT-02160	654	Medium level
11		Bus Stop Area	LT-02161	264	Medium level
12		Area-10	LT-02162	214	High level
13		Hotel Parking Area	LT-02157	2156
14		Area-12	LT-02164	263	Normal level
15		Area-7	LT-02165	454
16		Area-6	LT-02166	545	High level
17		Bus Stop Area	LT-02161	154	Medium level
Sheet1

 

[steve the fish]

Maybe try this:

=IF(FILTER(INDEX($B$3:$E$100,,MATCH(G$2,$B$2:$E$2,0)),((($E$3:$E$100="High Level")+($E$3:$E$100=""))*($B$3:$B$100<>"")))="","",FILTER(INDEX($B$3:$E$100,,MATCH(G$2,$B$2:$E$2,0)),((($E$3:$E$100="High Level")+($E$3:$E$100=""))*($B$3:$B$100<>""))))

 

[mmr1]

Thanks for the formula help,

another request, can i get a solution for this without spill formulas because i need to share a file various peoples where they are doesn't have office 365.

Secondary thing is
I have needed conditional formatting to the list-1 so then quickly verify in large data and highlight the rows of High Level, blank Cells, that the formulas in Column G to J are correct.


Thanks,