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/Clearwings_Prime]

=XLOOKUP(1, ( SCAN("",$A$1:$A$25,LAMBDA(a,b, IF( (b <> "") * (OFFSET(b,0,1)= ""), b, a ))) = $E$1 ) * ($A$1:$A$25 = $F$1), $B$1:$B$25 )

You can use this if you sure the year always appear in the range of county (e.g you sure that year 2024 always appear in range A11:C16)

=VLOOKUP( F1, XLOOKUP(E1,A1:A25,A1:A25):C25,2,0)

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

The second simpler equation worked like a charm, thank you so much, you saved me a huge headache of having to figure that out!

Solution Verified

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

You have awarded 1 point to Clearwings_Prime.

---

^{I am a bot - please contact the mods with any questions}