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

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
1List-1List-2
2Work LocationWork Notification RefEstimated Quantity of rectification in MetersPriorityPriorityWork LocationWork Notification RefEstimated Quantity of rectification in Meters
3Area-1LT-02153100High level
4Hotel Parking AreaLT-0215415Medium level
5Area-1LT-02153214
6Area-4LT-02156152Normal level
7Car Parking AreaLT-02157365
8Area-6LT-02158145Normal level
9Car Parking AreaLT-02159142Normal level
10Area-8LT-02160654Medium level
11Bus Stop AreaLT-02161264Medium level
12Area-10LT-02162214High level
13Hotel Parking AreaLT-021572156
14Area-12LT-02164263Normal level
15Area-7LT-02165454
16Area-6LT-02166545High level
17Bus Stop AreaLT-02161154Medium level
Sheet1
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 25, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,679
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top