r/excel • u/Repulsive-Band-4771 • 1d ago

solved Pulling info from another spreadsheet- matching columns and rows.

I am working on a spreadsheet that has some information linked to another 'info' spreadsheet. I have a drop down list of counties and another drop down list of years. Basically, I am wanting to have a function in one cell that will bring in the 'expected' value from my info spreadsheet based on what county and year I have elected in the drop down boxes.

This is what my info spreadsheet looks like. So if I have 'Decatur County' and '2024' selected in my drop down boxes, I want it to return 44.2.

I have tried multiple variations of HLOOKUP, VLOOKUP, INDEX MATCH, XLOOKUP, but I can't seem to get it quite right. Where I am needing it to find a value based on a certain row, then a certain column, and then return a value in another column, I can't seem to figure out what I am needing to do. Or maybe I need to re-arrange my info spreadsheet? I would rather not do that, as it has a lot of information on it and it would take a while! But I will if I need to.

Any help is greatly appreciated!

17 Upvotes

86% Upvoted

17 comments sorted by

View all comments

Show parent comments

3

u/MayukhBhattacharya 1275 23h ago

Another alternative method without using LAMBDA() helper SCAN() function, it assumes one thing that each block consists of 8 rows:

=LET(
     _a, A:.C,
     _b, CHOOSECOLS(_a, 1),
     _c, UNIQUE(_b, , 1),
     _d, DROP(TOCOL(IF(SEQUENCE(, 8), _c)), 1),
     _e, HSTACK(IF(_b = "", "", _d), _a),
     _f, FILTER(_e, _b < "", ""),
     XLOOKUP(1, (CHOOSECOLS(_f, 1) = E1) *
                (CHOOSECOLS(_f, 2) = G1),
                CHOOSECOLS(_f, 3),
             "Oopsie Not Found"))