I created a triple nested XLOOKUP formula. Is there a more efficient way to do what I'm doing?
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 |
[link] [comments]
Want to read more?
Check out the full article on the original site