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

#### mmr1

##### Board Regular
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### steve the fish

##### Well-known Member
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
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,

Replies
3
Views
270
Replies
3
Views
484
Replies
5
Views
415
Replies
3
Views
448
Replies
1
Views
543

1,126,953
Messages
5,621,805
Members
415,857
Latest member
braunReivn

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

### Which adblocker are you using?

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

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