Vlookup with conditions / when there are multiple results

Koala123

New Member
Joined
Apr 13, 2019
Messages
24
Office Version
  1. 365
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!

Capture.PNG
 
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?
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Ok how about like
Excel Formula:
=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)))
 
Upvote 0
Ok how about like
Excel Formula:
=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!

Capture2.PNG
 
Upvote 0
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") )
 
Upvote 0
How about
Excel Formula:
=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.
 
Upvote 0
How about
Excel Formula:
=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.

Excel Formula:
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") )
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top