Search code examples
excelexcel-formulavlookupindex-match

How do I retrieve value in Column B Given 2 Criteria on the Same Row in an excel Formula

I have a table similar to the one below and am trying to get column B data based on the criteria in Column A and C. A = UserID, B = Description, C = Cost_Priority, D Cost. The table lists each userID, and common problem description, ranking the cost for the problem description and the cost of the problem description. This is a supplied table that I am working from.

UserID Problem_Description Cost_Priority Cost
111 Problem A 1 395.00
111 Problem B 2 200.00
111 Problem C 0 150.00
111 Problem D 0 145.00
112 Problem G 1 800.73
112 Problem S 2 200.46
112 Problem T 0 100.51

Resulting Table should look like the one below where UserID is Given along with the columns that define the Cost Priority Required. The problem I am having is getting the problem description based on static values in the User ID columns static values of 1 for Highest cost problem and 2 for the 2nd highest cost problem.

UserID Highest Cost Problem 2nd Highest Cost Problem
111 Problem A Problem B
112 Problem G Problem S

I have tried using a vlookup method to grab the USERID and compare Cost_Priority to 1 or 2 in an if statement but it was returning the Problem Description column in order including where Cost Priority was 0. I was wondering if someone else had any other ideas to populate the 2nd and 3rd columns in the 2nd table.

Solution

  • If you are on Microsoft 365 then try below formula. Drag down and across as needed.

    =INDEX(SORT(FILTER($B$2:$C$8,($A$2:$A$8=$A12)*($C$2:$C$8>0)),2,1),COLUMN(A$1),1)

    For older versions of excel try below array formula-

    =INDEX($B$2:$B$8,MATCH(1,($A$2:$A$8=$A12)*($C$2:$C$8=COLUMN(A$1)),0))

    Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.

    enter image description here