Creating an autofill with multiple sources

[DPChristman]

I am trying to create a spreadsheet with auto-fills based on multiple pieces of information on a separate tab.

For example, in 2022, location number 123 might be in San Jose, CA.

However, in 2023, location number 123 was in San Francisco, CA.

With only once sources (ex. Location number), this is easy with a simple vlookup formula.

However, now the lookup depends on two pieces of information (year and store number).

So for example if the year column (column A) is 2022, and the location column (column B) is 123, the lookup formula in column C will bring up San Jose, CA.

This is something I am trying to put together for the first time, so I don't have any examples to post.

Is there an easy way to do this?

If I was just pulling a numeric value, I know that I could use a SUMIFS formula, but that doesn't work if you are pulling up non-numeric values.

 

[Joe4]

Perhaps an IF statement, referencing the two different VLOOKUP formulas, i.e. something structured like this:
=IF(some condition, vlookup formula 1, vlookup formula 2)

It all depends on how your data is structured. Without seeing an example of your data, we cannot really provide specifics.

 

[DPChristman]

Perhaps an IF statement, referencing the two different VLOOKUP formulas, i.e. something structured like this:
=IF(some condition, vlookup formula 1, vlookup formula 2)

It all depends on how your data is structured. Without seeing an example of your data, we cannot really provide specifics.

As I said, I haven't got anything put together yet, but this is an example of the data source tab, and how it would fill the entry tab.
Entering both the year and location number would autofill the remaining columns from the data source. The Data Entry would be on One Tab, and the data Source would be on the second.

 

[Joe4]

One quick and simple solution would be to create a "helper" column that sows your Year and Loc# fields together in the data you are looking up into, i.e.

=A3 & B3

Then you can use easily use a VLOOKUP or XLOOKUP formula to get what you want.
The nice thing about using XLOOKUP is you can place this helper column at the end of your data (instead of at the beginning, like VLOOKUP would require).

 

[DPChristman]

One quick and simple solution would be to create a "helper" column that sows your Year and Loc# fields together in the data you are looking up into, i.e.

=A3 & B3

Then you can use easily use a VLOOKUP or XLOOKUP formula to get what you want.
The nice thing about using XLOOKUP is you can place this helper column at the end of your data (instead of at the beginning, like VLOOKUP would require).

That is an interesting alternative. We use helper columns in other spreadsheets. Kicking myself for not thinking of it. Each year will contain thousands of location entries.
I could likely convert all previous years to text, rather than formulas to reduce the file size. Thanks

 

[Joe4]

You are welcome.

Here is an article which shows another way too: Two-column Lookup in Excel

 

[DPChristman]

You are welcome.

Here is an article which shows another way too: Two-column Lookup in Excel

Thanks. I am actually liking the helper column option. I can fill the entire foreseeable columns with =IF(B2="","",(A5&B5)), and it will only calculate when a location number is entered.

 

[Peter_SSs]

That is an interesting alternative. We use helper columns in other spreadsheets. Kicking myself for not thinking of it. Each year will contain thousands of location entries.
I could likely convert all previous years to text, rather than formulas to reduce the file size. Thanks

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.