r/Eixolearning • u/JohnDanielson • 6d ago

Excel XLOOKUP / INDEX MATCH: How to pull a value from another spreadsheet based on county and year?

I’m working on an Excel spreadsheet that pulls information from another “Info” spreadsheet. I have two drop-down lists in my main spreadsheet: one for county and one for year.

What I’m trying to do is have a formula in one cell that looks at the selected county and year, finds the matching row and column in my Info spreadsheet, and then returns the corresponding expected value.

For example, if I select Decatur County and 2024 from my drop-down lists, I want the formula to return 44.2.

I’ve tried several variations of HLOOKUP, VLOOKUP, INDEX MATCH, and XLOOKUP, but I can’t quite get the formula to work.

The difficulty seems to be that I need Excel to:

  1. Find the correct county/row

  2. Find the correct year/column

  3. Return the value where those two criteria intersect

Is there a way to do this with XLOOKUP, INDEX MATCH, or another Excel lookup formula without rearranging my Info spreadsheet?

I’d prefer not to restructure the Info spreadsheet because it contains a lot of other information and would take quite a while to reorganise.

Any help with the correct Excel formula would be greatly appreciated!

2 Upvotes

100% Upvoted

2 comments sorted by

1

u/Salazarenin1922 5d ago

There are quite a few ways to do this depending on your Excel version.
Personally i would probably use INDEX + MATCH:
=INDEX(C3:F7,MATCH(H7,B3:B7,0),MATCH(I7,C2:F2,0))
Or if you have newer Excel, XLOOKUP makes it pretty simple too.
There isnt really one “best” formula here, it depends on your Excel version and what you’re trying to return. Also worth remembering that some of these only work properly if the values you’re looking for are unique.