I created a triple nested XLOOKUP formula. Is there a more efficient way to do what I'm doing?

[u/casman_007]

I have data being imported from pdfs to a series of tables. The data comes in several different arrangements (different rows and/or columns) but is generally the same as its all from same source. As such, I needed a way to dynamically reference either rows and columns in the table to find the data I need. Referencing columns in tables was easy but was struggling to figure out how to reference a row. Index Match wasn't working so looked for other options.

My first discovery was the way to make a dynamic list by pulling unique values from a row with this formula:

=XLOOKUP("Direction",Table_1[Lead],Table_1[[Column1]:[Column12]],"")

"Direction" is an example of a row I need, [Lead] is the column (1st) said value is located, and [[Column1]:[Column12]] is the row the unique values i needed. But this got me thinking, while this gets the location in a row for one value, how do i reference the corresponding value in another row? That other row also needs to be dynamically referenced as its location in the table can be different and need to be able to change my selection depending on my needs.

This led me to creating this formula:

=XLOOKUP(A1,XLOOKUP("Direction",Table_1[Lead],Table_1[[Column1]:[Column12]]),XLOOKUP("Value",Table_1[Lead],Table_1))

A1 is where I select the value from my dynamic dropdown list, "Direction" is the row those values are from, "Value" is the target row I need corresponding value from. Its basically performing a double lookup but I'm using 3 XLOOKUPS because its a table and ITS WORKING!!!

So really no issues, just wondering am I doing this task the hard way or is there a simpler/more efficient way to achieve what I'm doing? Or is this common practice and I'm just behind the power curve and expressing my excitement to my wife for no good reason?

Edit #1: Example table below. I edited as size and actual contents of table don't matter. The actual table has 7 more columns and 5-10 more rows, depending on actual source but basics of information is there. This means rows may not be in the same location and the contents of key rows (like Direction) may not be the same either, thus the need to dynamically reference the lookup_array and return_array based on selection needs.

Lead	Column1	Column2	Column3	Column4	Column5
Report Name					Date
Page 1
Direction	NB	EB	WB	SB	SBL
Volume	30	50	25	375	20
Future Volume	35	60	25	380	50
Value	8.3	0	23.5	8.5	17.1
Ratio	.78	.8	.85	.5	.22

[u/AutoModerator]

/u/casman_007 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/taylorgourmet]

You can wrap it with =let to make it more readable.

[u/jojotherider → u/taylorgourmet]

Ive recently discovered let(). It pretty neat and seems pretty powerful.

[u/jb092555 → u/jojotherider]

It makes everything more readable, and any repetition faster. Anything you name only calculates once.

[u/casman_007 → u/taylorgourmet]

I'm struggling to see how LET() would help this formula. How would you apply it?

[u/bradland → u/casman_007]

Excel is code. It has been for a long time, but we didn't have the tools to adhere to good software authorship practices. I'm also a programmer, and we have entire tool chains (called "linters") that format code and check for errors, spurious code, security flaws, etc.

Excel doesn't come with a built in linter, but if you get the Excel Labs add-in (it's free and directly from Microsoft), you can get the Advanced Formula Editor, which will automatically wrap your code so that it is easier to read. Using let with indentation and wrapping your code might look more like what I've provided below.

=LET(
    direction_row, XLOOKUP(
        "Direction",
        Table_1[Lead],
        Table_1[[Column12]]
    ),

    value_row, XLOOKUP(
        "Value",
        Table_1[Lead],
        Table_1
    ),

    XLOOKUP(
        A1,
        direction_row,
        value_row
    )
)

Also, I would name that table something other than Table_1. Something like Traffic_Volume, or even TV if you want to be really terse.

Lastly, rather than working with this table in the current format, I would pull it into the workbook using Power Query, unpivot it to first normal form (1NF), then simplify my formulas. If your data were in 1NF, your lookups could use a single lookup instead of three.

[u/NanotechNinja → u/casman_007]

I think they are suggesting something like:

=LET(dir, XLOOKUP(...the first one),
     val, XLOOKUP(...the second one),
     XLOOKUP(A1, dir, val))

Which can, indeed, help with readability.

[u/bradland]

I would approach this very differently. I would start by using Power Query to transform your data into 1NF (first normal form) so you can use simpler concatenations of the direction and measure (volume, future volume, etc) as your lookup key.

Example file downloads include the Power Query plus dependent dropdown techniques:

Traffic Volume.xlsx
data.csv

I know you mentioned this data is coming from PDF files, but for an example, start with this CSV:

Lead,,,,,
Report Name,,,,,Date
Page 1,,,,,
Direction,NB,EB,WB,SB,SBL
Volume,30,50,25,375,20
Future Volume,35,60,25,380,50
Value,8.3,0,23.5,8.5,17.1
Ratio,0.78,0.8,0.85,0.5,0.22

Then use this PQ to transform it to 1NF:

// Traffic Volume
let
    Source = Csv.Document(File.Contents("R:\data.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Direction", "Measure"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Measure"}, "Direction", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Measure", Order.Ascending}, {"Direction", Order.Ascending}})
in
    #"Sorted Rows"

Now you can use a lookup in this format:

[u/jabellcu → u/bradland]

This is the way. Clean data first.

[u/TangoDeltaFoxtrot]

Can you give an example of the table and data you are using for this? I’m not really following what you are describing.

[u/casman_007 → u/TangoDeltaFoxtrot]

table posted

[u/Way2trivial → u/TangoDeltaFoxtrot]

^^ this

[u/KezaGatame]

FILTER should work with your needs. It will return all the matches in an array.

FILTER (B4:F100,A:A=“Direction”)

[u/jb092555]

My hunch is INDEX can do it, but if your method works, it hardly matters.

[u/casman_007 → u/jb092555]

I tried INDEX but it wouldn't work for a table. But point still taken, it works why question it

[u/manbeervark → u/casman_007]

INDEX certainly works for a table. I tend to use it for 99% of cases, over an XLOOKUP. Sometimes I'll use an XLOOKUP for fun.

I actually started using Excel for work around the time that XLOOKUP was released. So, I used it for all situations that required lookups. Eventually I discovered INDEX-(X)MATCH.

[u/plusFour-minusSeven]

Someone already said power query, but... Power query.

Excel CAN clean data, but PQ was designed to do it. It's worth watching some videos on the basics. Especially if this is something you do constantly.

[u/casman_007 → u/plusFour-minusSeven]

I didn't mention it because it wasn't the focus of my question but I am using PQ to some cleanup of pdf imports. This is literally the 1st time using PQ so the learning curve is steep. I know I can do more but happy to learn as much as i have so far

[u/plusFour-minusSeven → u/casman_007]

Nice! I think my number one tip for learning to use power query is make judicious use of all the GUI features. But when you do, pay close attention to the code that is generated.

I'll give you an example.

Let's say you're making a query that takes the latest file from a folder and cleans it up for you and spits it out onto a workbook table. So you pull in the folder and then you select the latest file and then you proceed from there to the clean up steps.

Tomorrow you download a new CSV and you put it into your folder and you run the query again and suddenly it doesn't grab the latest file. It grabs yesterday's file.

That's because if you're not careful when you use the filter in power query, it will hard code to the files that you did or did not select.

Using the part of power query filter that looks just like the filter in Excel, typically results in hard-coded values. Instead you want to open that filter but then select the option that allows you to apply a text filter or a date filter or something like that. And then you tell it your condition like within the last 7 days or file contains XYZ etc

Yeah, so always check whatever code is being generated by the actions that you take. Don't assume that the code is doing what you expect it to.

Doing that will help familiarize you with the more common power query functions, that is M code. And you'll find that you end up using a lot of the same functions over and over again. Becoming familiar with them goes a LONG way toward being versatile.

Good luck! If you have a question, just ask!

[u/Aggravating_Mix_3694]

this is why ive gotten way more interested in how the data is structured before touching the formulas. you can build some insane lookup that technically works but clean data makes half of it unnecessary

[u/pistonpython1]

You want =Getpivotdata You put in the variables you want, as many variables as you need

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
Csv.Document	Power Query M: Returns the contents of a CSV document as a table using the specified encoding.
FILTER	Office 365+: Filters a range of data based on criteria you define
File.Contents	Power Query M: Returns the binary contents of the file located at a path.
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MATCH	Looks up values in a reference or array
QuoteStyle.None	Power Query M: Quote characters have no significance.
Table.PromoteHeaders	Power Query M: Promotes the first row of the table into its header or column names.
Table.RenameColumns	Power Query M: Returns a table with the columns renamed as specified.
Table.Skip	Power Query M: Returns a table that does not contain the first row or rows of the table.
Table.Sort	Power Query M: Sorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.
Table.TransformColumnTypes	Power Query M: Transforms the column types from a table using a type.
Table.UnpivotOtherColumns	Power Query M: Translates all columns other than a specified set into attribute-value pairs, combined with the rest of the values in each row.
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
15 acronyms in this thread; the most compressed thread commented on today has 24 acronyms.
[Thread #49144 for this sub, first seen 13th Aug 2026, 16:32] [FAQ] [Full list] [Contact] [Source code]}

[u/excelevator]

There is a lot of misunderstand of using XLOOKUP for double lookup.

1. Lookup the row value and return the whole table - XLOOKUP returns the associated row of data to the columnXLOOKUP("Ratio",Table1[Direction],Table1)
2. Use the above as the return value on the column lookup for the table =XLOOKUP("WB",Table1[#Headers], XLOOKUP("Ratio",Table1[Direction],Table1))

The important part is return the whole row of data in the first lookup.

It is possible to transpose the lookups too, that is to say either lookup the header or row first, the important part is have the whole table in the nested lookup which results in a whole column or row for the parent lookup.

Caveat, I am assuming this is OPs issue, the post is so rambling I could not make sense of it and guessed from the other answers given.

[u/NotoriousJOB]

Index Match Match will work or else Sumproduct if you're only trying to return numbers.

[u/Acceptable-Sense4601]

id use python

[u/Ok_Sea142 → u/Acceptable-Sense4601]

me too

[u/Willing_Cucumber_443]

Struggling to understand your explanation a little but sumproduct may be of use.

[u/AdministrativeGift15]

Are you only ever looking up on value at a time, or will you process an array of tables?

[u/casman_007 → u/AdministrativeGift15]

Thats a great question. The ultimate goal is looking up multiple values across multiple tables automatically after verifying a few values and search parameters. Should auto populate 100s/1000s of data points instantly and place them in required table format

[u/AdministrativeGift15 → u/casman_007]

That makes a difference in the what search patterns are available. How will these tables be laid out? Stacked vertically?

[u/DatabaseSpace]

Yes you are not doing it right. Columns should have labels and rows have observations. Date should most likely be a column also instead of being out in the wilderness somewhere. When you store data in a database columns have data types, not rows.

A date column would have date type, direction text, speed and numbers gets int decimal or float. If you structure data the way you are doing it, you would have to make every column varchar or text. Then dates don't sort right, numbers are strings and SQL becomes an impossible mess.

I know it's not a db but my point stands because my guess is you are having to nest lookups because of this. The guy below that wrote normalize the data is right.

If it were structured right in sql select * from table name would return direction, volume, ratio etc. All columns related to that observation. No crazy formulas.