CODEWITHSUNDEEP
excel-formula
Alex Midmore
Alex Midmore

Reputation: 47

How can I perform a VLOOKUP (or similar) that prioritises certain values?

I have a list of emails and UTM sources that I need to perform a lookup on to return the source, but I need the formula to default to certain values if they are present. See example table below

|email       |source  |
|------------|--------|
|[email protected] |email   |
|[email protected] |direct  |
|[email protected] |organic |
|[email protected] |direct  |

In this case I want the direct source to be chosen over any other, however VLOOKUP will select the first value corresponding to the lookup value. I've no idea where to start beyond a basic VLOOKUP. I've tried INDEX(MATCH( but it just seems to do the same thing.

I've tried to find a solution to similar examples but in every case I've come across it involves numeric values which can be prioritised using MIN/MAX. Is this something I could do more easily in Power Query? Could I just put a 1 in front of direct and then sort the source column A-Z? This seems to work but I'm unsure of how reliable it is before running with it.

Upvotes: 0

Views: 1154

Answers (1)

mark fitzpatrick
mark fitzpatrick

Reputation: 3322

If I am understanding well, you want to return the Source by a custom order where Direct has priority over all others and the problem is that VLOOKUP, INDEX(MATCH, and XLOOKUP all deliver the first (or last) match alphabetically with no respect to your custom priority, so they won't give you the results you want.

I see two approaches that are illustrated below. To make this more clear, I took your table and converted it into an Excel Table (CRTL t) that I named tContacts and I added a third email address and a source of "social" to it to illustrate the advantage of method 2.

tContacts

Quick Answer

You can jump straight to NOTES below and implement Method 1 with a Named Range to test if this really works. The rest is just an explanation of how it works and the choices you have if I have misunderstood or you want to modify it further.

METHOD 1 XISs - Keep it simple, no helpers

Here, you will put in a lookup value (I put mine in D9) that I named lookupVal for clarity of understanding. I used LET to make this more clear and speed up calc, but it is not required - you can collapse everything into the final XLOOKUP below if you prefer.

=LET( lookupVal, D9,
       sTable, SORTBY( tContacts, -(tContacts[source] = "direct") ),
       XLOOKUP( lookupVal, INDEX( sTable, , 1 ),INDEX( sTable, , 2 ) ) )

This creates a sort table that I called sTable § that uses SORTBY to sort your contacts table (tContacts) according to your rule that "direct" has priority over all other sources using this boolean statement -(tContacts[source] = "direct"). So sTable is tContacts sorted by your rule, like this:

sTable simple rule

It does an XLOOKUP of lookupVal against the column 1 of sTable and delivers column 2 of sTable as the result.

METHOD 2 XISt - Extended Priorities with helper table

But what if your priorities are more complex? If you wanted to extend your priorities, you could create a table for sorting them that I will call tSrcPri.

tSourcePrio

This is why I added a third email to the table and added a fictional social so that we can see the value of structured priorities where direct > social > organic > email. Here is the formula:

=LET( lookupVal, D9,
       sTable, SORTBY( tContacts, XLOOKUP( tContacts[source], tSrcPri[SortOrder], tSrcPri[SortKey] ) ),
       XLOOKUP( lookupVal, INDEX( sTable, , 1 ),INDEX( sTable, , 2 ) ) )

You can see that the only difference is that sTable is now sorted by an XLOOKUP of the tContacts source against the tSrcPrio to return a numerical SortKey that obeys your priority rules. In this way, sTable is extended to include a rich set of priorities instead of just "is direct or not direct".

sTable extended

Comparisons

Now we can put all of the methods side-by-side and you can see their results:

comparison

You can now see the difference betwen XISs and XISt in email3.

NOTES

§ As you will likely be applying this formula many times, you could speed up the calculations by creating a Named Range that holds sTable. Let's say that you decide to name it _sTable:

In Method 1, _sTable would be set to:

SORTBY( tContacts, -(tContacts[source] = "direct") ).

In Method 2, _sTable would be set to:

SORTBY( tContacts, XLOOKUP( tContacts[source], tSrcPri[SortOrder], tSrcPri[SortKey] ) )

Then your XISs or XISt would be simplified to:

XLOOKUP( D9, INDEX( _sTable, , 1 ),INDEX( _sTable, , 2 ) )

Upvotes: 1

Related Questions