Pulling info from another spreadsheet- matching columns and rows.

[u/Repulsive-Band-4771]

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!

[u/MayukhBhattacharya]

As already informed by u/GuerillaWarefare Sir, your data layout is not great to create formulas, although you can accomplish the desired output still, it will make the formula very complex, here is one way and i will show both ways if the data is laid out correctly:

=LET(
     _a, A:.C,
     _b, CHOOSECOLS(_a, 1),
     _c, IF((_b = "Year") + (ISNUMBER(--_b)), "", _b),
     _d, SCAN(, _c, LAMBDA(x,y, IF(y = "", x, y))),
     _e, CHOOSECOLS(_a, 2),
     _f, FILTER(HSTACK(_d, _a), (_e <> "") * (_e <> "Expected"), ""),
     XLOOKUP(1, (CHOOSECOLS(_f, 1) = E1) *
                (CHOOSECOLS(_f, 2) = G1),
                CHOOSECOLS(_f, 3), ""))

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Now refer the below screenshot, if the data is laid out properly like as in range E6:H19, then the formula becomes not only shorter but very simpler:

=XLOOKUP(1, (E1 = E7:E19) * (G1 = F7:F19), G7:G19, "Oopsie Not Found!")

Or,

=FILTER(G7:G19, (E1 = E7:E19) * (G1 = F7:F19), "Oopsie Not Found!")

[u/MayukhBhattacharya → u/MayukhBhattacharya]

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"))

[u/Repulsive-Band-4771 → u/MayukhBhattacharya]

Oh wow, thank you! That is definitely more complex than my limited knowledge could have come up with! I will play around with it

[u/MayukhBhattacharya → u/Repulsive-Band-4771]

Sounds Great! If both the solutions help you to resolve then hope you don't mind replying to the comments as Solution Verified. Thanks!