Vlookup with conditions / when there are multiple results

[Koala123]

Hi pros, wondering if anyone can help?

I want to search IDs in tab 1, from tab 2 (the below pic) and return to AccountNumber values. As you can see one ID could have multiple results, some status are "NULL" and some are "1".

I understand that Vlookup will return to the first result it finds, which in my case could be an AccountNumber marked as "NULL".

Basically we need values with "1" when there are multiple identical IDs, if a ID is unique then we can disregard the status, then it will be a normal Vlookup, but when there are multiple results Vlookup may return to a "NULL" AccountNumber which is not we want.

What should I do to make sure it always return to AccountNumber marked as "1" when there are multiple results? Thanks heaps!

 

[Koala123]

I think it can be done quite simply
First sort the data on the Status column, which will push the NULLS to the bottom. Then just do a VLOOKUP
You can do this in one formula by doing the sort in a LET variable first, then doing VLOOKUP on the result

Good one! This would work if data is overwrote each time, however currently accounts data is added on top of previous data in a master data sheet, in chronological order. I know we can sort the data before paste to master data sheet, but just I am doing this for a process improvement purpose, so copy & paste is done by macro now, I am just seeking if we can do this with minimum human intervention.

Would it be possible to solve it with formulas?

 

[Fluff]

Ok how about like

=LET(f,FILTER(Sheet1!$B$2:$C$1000,Sheet1!$A$2:$A$1000=A2),IF(ROWS(f)=1,INDEX(f,1),INDEX(FILTER(f,INDEX(f,,2)=1),1)))

 

[Koala123]

Ok how about like

=LET(f,FILTER(Sheet1!$B$2:$C$1000,Sheet1!$A$2:$A$1000=A2),IF(ROWS(f)=1,INDEX(f,1),INDEX(FILTER(f,INDEX(f,,2)=1),1)))

Hi Fluff, thanks a lot! This looks like really powerful. Could I double check on the column numbers? Sorry I didn't include column letter in previous screenshot.

I have played around your formula but didn't get the right AccountNumber, Can I ask if I want to use this formula in cell I31, how would I update this formula?

thank you!

 

[MEUserII]

Hi pros, wondering if anyone can help?

I want to search IDs in tab 1, from tab 2 (the below pic) and return to AccountNumber values. As you can see one ID could have multiple results, some status are "NULL" and some are "1".

I understand that Vlookup will return to the first result it finds, which in my case could be an AccountNumber marked as "NULL".

Basically we need values with "1" when there are multiple identical IDs, if a ID is unique then we can disregard the status, then it will be a normal Vlookup, but when there are multiple results Vlookup may return to a "NULL" AccountNumber which is not we want.

What should I do to make sure it always return to AccountNumber marked as "1" when there are multiple results? Thanks heaps!

View attachment 55123

From your data set image in your post: with the ID values entered in to: $A$2:$A$29, with the AccountNumber values entered in to: $B$2:$B$29, and with the Status values entered in to: $C$2:$C$29; then the formula to return the desired results specified is below - entered in to D2 and copied down.

D2:= =IF(((COUNTIFS(($A$2:$A$29), ($A2) ))>(1) ), (MID((VLOOKUP((($A2)&("_")&(1)&("*") ), (($A$2:$A$29)&("_")&($C$2:$C$29)&("_")&($B$2:$B$29) ), (1), (FALSE) ) ), ((FIND((CHAR(1) ), (SUBSTITUTE((VLOOKUP((($A2)&("_")&(1)&("*") ), (($A$2:$A$29)&("_")&($C$2:$C$29)&("_")&($B$2:$B$29) ), (1), (FALSE) ) ), ("_"), (CHAR(1) ), (2) ) ) ) )+(1) ), ((LEN(VLOOKUP((($A2)&("_")&(1)&("*") ), (($A$2:$A$29)&("_")&($C$2:$C$29)&("_")&($B$2:$B$29) ), (1), (FALSE) ) ) )-(FIND((CHAR(1) ), (SUBSTITUTE((VLOOKUP((($A2)&("_")&(1)&("*") ), (($A$2:$A$29)&("_")&($C$2:$C$29)&("_")&($B$2:$B$29) ), (1), (FALSE) ) ), ("_"), (CHAR(1) ), (2) ) ) ) ) ) ) ), ("NOT MULTIPLE IDENTICAL IDs") )

 

[Fluff]

How about

=LET(f,FILTER(Sheet1!$C$2:$E$1000,Sheet1!$B$2:$B$1000=I31),IF(ROWS(f)=1,INDEX(f,1,2),INDEX(FILTER(f,INDEX(f,,3)=1),1,2)))

@MEUserII in future could you please post your formula in tags as it makes it a lot easier to read & copy/paste. It's the xls icon in the reply window.

 

[MEUserII]

How about

=LET(f,FILTER(Sheet1!$C$2:$E$1000,Sheet1!$B$2:$B$1000=I31),IF(ROWS(f)=1,INDEX(f,1,2),INDEX(FILTER(f,INDEX(f,,3)=1),1,2)))

@MEUserII in future could you please post your formula in tags as it makes it a lot easier to read & copy/paste. It's the xls icon in the reply window.

@Fluff, nice formula solution; as well as a thank you for helping me in the past with Excel questions; it's nice to be able to pay it forward and help others.

I've updated the formula tag to the format suggested.

D2:= =IF(((COUNTIFS(($A$2:$A$29), ($A2) ))>(1) ), (MID((VLOOKUP((($A2)&("_")&(1)&("*") ), (($A$2:$A$29)&("_")&($C$2:$C$29)&("_")&($B$2:$B$29) ), (1), (FALSE) ) ), ((FIND((CHAR(1) ), (SUBSTITUTE((VLOOKUP((($A2)&("_")&(1)&("*") ), (($A$2:$A$29)&("_")&($C$2:$C$29)&("_")&($B$2:$B$29) ), (1), (FALSE) ) ), ("_"), (CHAR(1) ), (2) ) ) ) )+(1) ), ((LEN(VLOOKUP((($A2)&("_")&(1)&("*") ), (($A$2:$A$29)&("_")&($C$2:$C$29)&("_")&($B$2:$B$29) ), (1), (FALSE) ) ) )-(FIND((CHAR(1) ), (SUBSTITUTE((VLOOKUP((($A2)&("_")&(1)&("*") ), (($A$2:$A$29)&("_")&($C$2:$C$29)&("_")&($B$2:$B$29) ), (1), (FALSE) ) ), ("_"), (CHAR(1) ), (2) ) ) ) ) ) ) ), ("NOT MULTIPLE IDENTICAL IDs") )