Double XLOOKUP

How a Good Thing Became Incredible

Author

Eamonn Cottrell
December 19, 2023

CODING & SPREADSHEETS
Double XLOOKUP

Since last we spoke I’ve…

  1. Run my first 5K in over a decade

  2. Run a 50K

  3. Started writing a book…

Now to the sheets…

Double XLOOKUP

By itself, XLOOKUP is amazing. It will let us select a lookup value (After Hours Blend), a lookup range (the column of coffees), and a result range (the tasting notes) so that you can lookup the correct notes of the corresponding coffee.

screenshot of coffee data

But if you have a more complicated lookup scenario, you can nest one lookup within another lookup.

Let me explain.

Take this grid of alphabets I made for a Vigenère Cipher:

screenshot of alphabet grid

If we have two things to lookup (the text to encode on the left side and the key to encode it with on the top row), then it’s not a simple case of XLOOKUP. We need to look up the text as well as the key.

Here’s what this madness looks like:

screenshot of double xlookup

What’s happening is:

  1. We look up the first letter to encode, in this case it’s an “a”

  2. We use the plaintext key as the lookup range (that’s the first column alphabet all the way to the left.

  3. Then we need to use the key column that is returned from the second XLOOKUP as the result range.

  4. If you look at the second XLOOKUP, you can see that we are using the letter “L” as the key letter.

  5. We look that up in the first alphabet row on the very top.

  6. And we use the entire grid as our result range. This lets us return the whole column that starts with L all the way down to K.

  7. It’s that whole column that’s used as our result range for the first XLOOKUP.

Neat trick, eh? It’s hard to conceptualize without looking at the sheet or watching a video, though 👇

Here’s the demo sheet I made for the Vigenère Cipher.

And the video walkthrough I did is below 👇

Thank you so much!

It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!

Would love to say hi. Here are the best places to find me:

✉️ LinkedIn
📺️ YouTube

Ways I can help:

  • Find all my video tutorials and walkthroughs on my YouTube channel.

  • View my spreadsheet and creative products on my Gumroad store.

  • Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems.

  • Hire an expert to help complete your next Google Sheets, Apps Script or Google Workplace project.

Reply

or to participate.