Return Value Based on Priority

Question

Dear All, I hope you guys had a great day! I would like to ask a few help:

I have a master file which include below table 1:

Sr	Serial No.	Status	Priority
1	AA11BB	Excellent	1
2	AA11BB	Good	2
3	CC77AA	Bad	3
4	CC88AA	Excellent	1

In another file, I have this table 2:

Serial No.	Status
AA11BB
AA11BB
CC77AA

What I'm trying to do is:

I'm trying to fill the status from table 1 in table 2.

The criteria is:

• Serial No. from table 2 should match with the Serial No. from table 1
• But formula should also look at the priority column from table 1 and only pick the highest priority which is 1. If 1 is not in the table 1, 2 would be the highest priority. So it should be dynamic.

The matching part is ok as I used index match formula but I don't know how to dynamically look up the priority number.

I appreciate any helps and have a great day!

Answer 1

=FILTER($C$2:$C$16,($B$2:$B$16=B2)*(MINIFS($D$2:$D$16,$B$2:$B$16,F2)=$D$2:$D$16))

Answer 2

Hi,

This M code in Power Query works

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sr", Int64.Type}, {"Serial No.", type text}, {"Status", type text}, {"Priority", Int64.Type}}), 

#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sr"}), 

#"Grouped Rows" = Table.Group(#"Removed Columns", {"Serial No."}, {{"All", each Table.Min(\_,"Priority")}}), 

#"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"Status"}, {"Status"}) 

in

#"Expanded All"

Hope this helps.

Answer 3

Hello,

I am facing an similar issue, but i cannot view the original file here and im confused about which column means which.

I see that Snow Lu has given an answer that seems to work for you, could you pls tell me which column is which? Thank you!!

Answer 4

Hi there,

Try using this:

=LET(sortedTable,SORTBY($D$5:$F$8,$F$5:$F$8,1),INDEX(sortedTable,MATCH(D13,CHOOSECOLS(sortedTable,1),0),2))

This assumes the whole table in the master file is in range D5:F8, where the priority levels are on range F5:F8. Also adjust the INDEX/MATCH ranges.

Martin