Populating a column in an Excel tab based on matching values from 2 columns in different tabs

I have an excel spreadsheet with two tabs.

Column A contains NumberValue1
Column C is empty

Tab 2
Column B contains NumberValue1
Column A contains AlphaValue1

I need to populate Column C from Tab 1 with Column A from Tab 2 where Column A from Tab 1 = Column B from tab 2.

How do I do this?
Mike MillerSoftware EngineerAsked:
Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
You also have a data type mismatch: '908863 FINAL' has the store numbers as true numbers, formatted to display a leading zero, while the store numbers in 'STORE LIST' are actually text.

Thus, my approach needs a slight tweak:


If no match is found, that formula will return a #N/A error.  This tweak will replace that error with an alternate value:

=IFERROR(INDEX('STORE LIST'!A:A,MATCH(TEXT(A4,"000000"),'STORE LIST'!B:B,0)),"!! No match !!")
This is not that hard, but you have to make some changes.

1) In tab 2, change the order of your columns, Put Column B as the first column.
2) in tab 1, on c1 =VLOOKUP(A1,Sheet2!$A$1:$B$10,1,).

In this case my tab 2 page is called Sheet2, so you need to change that and also, change the range, In my case I use 10 rows so it goes from a1 to b10. the $ symbol is to lock the values so you can paste it without problems.
Mike MillerSoftware EngineerAuthor Commented:
Is there a way to specify an undetermined amount of rows, (instead of 10?)
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Patrick MatthewsCommented:
To do it without flipping the columns:

sure you can do it with as many rows as you want, just replace the 10 in the formula with the last row you want to search.
Mike MillerSoftware EngineerAuthor Commented:
I've got both solutions working on a spreadsheet I just created but can't get it to work on my initial spreadsheet.

The data on tab1 does not begin until line 4
The data on tab2 does not begin until line 2

Still playing around with it...
ok, just change the initial value, in my solution something like this: =VLOOKUP(A4,Sheet2!$A$2:$B$10,1,).

Notice, I replaced the first A1 with an A4 (row 4) and the A$1 with A$2. This should work.

For matthewspatrick's solution


notice, I replaced a2 with a4, where your data begins. I'm not sure if you have to change any other value.
Mike MillerSoftware EngineerAuthor Commented:
Can you take a look at the attached spreadsheet? I cannot figure this out for the life of me.
Change the name of the sheets on the formulas. From sheet1 and sheet2, to the name of your sheets (tabs). Also, because you have spaces in your names put the sheet name between ', example, ' sheet name'
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.