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!

 

[Dermot]

I understand that you want to skip NULL rows, but I see that ID 80048412 has two rows, both with a 1.
Which one do you want?

 

[Koala123]

I understand that you want to skip NULL rows, but I see that ID 80048412 has two rows, both with a 1.
Which one do you want?

Hi Dermot, it doesn't matter as long as it's 1, just want to skip NULL. NULL rows are also ok when ID is not multiple.

 

[Dermot]

What is your Excel version?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what should happen with IDs like 80048412 which have multiple accounts with 1 in the status column?

 

[Koala123]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what should happen with IDs like 80048412 which have multiple accounts with 1 in the status column?

Hi Fluff, I use Microsoft Office 365.

Sorry I should have summarized all conditions, the result I would like to achieve is:

1. ID with multiple accounts, including both NULL and 1 : avoid NULL, return to 1 <----------- this is why I raised this question
2. ID with multiple accounts, all with 1: all acceptable
3. ID with only one account : all acceptable regardless status
3. ID with multiple accounts, all with NULL: all acceptable ( I think we don't have this scenario but just in case)

 

[Koala123]

What is your Excel version?

Office 365 : )

 

[Fluff]

When there are multiple accounts that are acceptable, do you just want to pull the 1st one?

 

[Dermot]

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

 

[Koala123]

When there are multiple accounts that are acceptable, do you just want to pull the 1st one?

Yes, it's good as long as status is 1