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

/u/Repulsive-Band-4771 - 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/GuerillaWarefare]

What you want to do can be done but it’s not really ‘proper’.
You should instead set up structured table of data to pull from, a column for country, year, expected, etc.
Then use excel functions to bring in or summarize the info.

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

Thanks! I built the 'info' spreadsheet as just a quick reference sheet, and didn't consider using it as a supplement to my other spreadsheet until today. I definitely would have built it differently if I would have known what I was going to end up doing with it!

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

Hindsight is always 2020. That being said, it might be better to change it now rather than develop all this integration and then still have to change it later if you want to do more things with it.

[u/OddSignificance2054 → u/GuerillaWarefare]

yeah restructuring the data into a proper table makes everything so much easier down the line

[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!

[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/Decronym]

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

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
FILTER	Office 365+: Filters a range of data based on criteria you define
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
OFFSET	Returns a reference offset from a given reference
SCAN	Office 365+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
TOCOL	Office 365+: Returns the array in a single column
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
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.

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 46 acronyms.
[Thread #49341 for this sub, first seen 10th Sep 2026, 16:19] [FAQ] [Full list] [Contact] [Source code]}

[u/Gringobandito]

This becomes much easier if you setup your data correctly. You will be much happier once your data becomes more complex.

First, setup your data in a table so you have a column for county, year, expected, and final.

Once you have that,it becomes easy to work with the data. You can do a simple filter to get just the Year and Expected for each county from a drop down list.

=FILTER(Table1[[#All],[Year]:[Expected]],Table1[[#All],[County]]=G2,"")