multiple condition lookup

[bigbavarian]

Hello everyone,

I know there have been different threads and ytoutube videos out there on this but I cannot seem to the it to work in my example.

I have attached a spreadsheet that I am working with. There is information on both tabs. I originally was going to do a vlookup but quickly discovered that when there are more than one row with the same person, it would only display the data from the first row.

I woulod like to take the data from two of the cells on the first tab to help pull back the correct information from the second tab.

Example, I would like to take ID abd PMPR from the first tab and bring back the correct Group, Co, and or amount from the second tab.

Appreciate it.

[NBVC]

In Query Results sheet add a helper column, so in M2 enter formula:

=A2&"_"&E2&"_"&COUNTIFS(A$2:A2,A2,E$2:E2,E2)

copied down to count duplicates.

Then in Rejects, J2 enter:

=INDEX('Query Results'!J$2:J$7,MATCH($E2&"_"&$F2&"_"&COUNTIFS($E$2:$E2,$E2,$F$2:$F2,$F2),'Query Results'!$M$2:$M$7,0))

copied across, change the 'Query Results'!J$2:J$7 range in each column to match the column of interest from the Query Results sheet, then copy these formulas down.

[bigbavarian]

Boy that is complicated and I am having a hard time following it. I can't even see how to adjust it to bring in the Group, CO or Amount field.

Nothing a little simpler huh?

[NBVC]

The formula in the helper column is simply concatenating the ID and DeptID fields in each row and at the same time counting how many times that combo repeats as you copy down. This is so we can identify subsequent matches to the first.


This formula below, then indexes each column in your Query results by matching again the ID and PMPR columns and the cumulative count of this combo as you copy down to the helper column we made.

If you had the columns in the Query Table in the same order as in the Rejects sheet, then it would be a matter of entering the formula once and copying down and over the matrix. But, since you don't have the columns organized, you need to just change the ('Query Results'!J$2:J$7 range to match the column you want to pull from in the column the formula is in.

=INDEX('Query Results'!J$2:J$7,MATCH($E2&"_"&$F2&"_"&COUNTIFS($E$2:$E2,$E2,$F$2:$F2,$F2),'Query Results'!$M$2:$M$7,0))

So to get the Group in column N, you need to change the indexed range to 'Query Results'!F$2:F$7. And to get the Amount, it's 'Query Results'!I$2:I$7.

There are other methods, but not really simpler in the end.

[bigbavarian]

Now I follow you and see how it is working.

I do have one follow up question. In some of the cells it has the little green triangle. Do you know how to keep that from displaying when it really isn;t an error?

Thank you again, This was very helpful.

[NBVC]

Select the range of cells, you should see a little caution sign icon come up to the left of the selection, click on it and select Ignore Error.

It is warning you about the inconsistent formula references