Solved

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

Posted on 2011-10-06
199 Views
I have an excel spreadsheet with two tabs.

Tab1
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?
0
Question by:mwmiller78

LVL 2

Expert Comment

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.
0

Author Comment

Is there a way to specify an undetermined amount of rows, (instead of 10?)
0

LVL 92

Expert Comment

To do it without flipping the columns:

=index(sheet2!a:a,match(a2,sheet2!b:b,0),1)
0

LVL 2

Expert Comment

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.
0

Author Comment

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...
0

LVL 2

Expert Comment

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

=index(sheet2!a:a,match(a4,sheet2!b:b,0),1)

notice, I replaced a2 with a4, where your data begins. I'm not sure if you have to change any other value.
0

Author Comment

Can you take a look at the attached spreadsheet? I cannot figure this out for the life of me.
Book1.xlsx
0

LVL 2

Expert Comment

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'
0

LVL 92

Accepted Solution

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:

=INDEX('STORE LIST'!A:A,MATCH(TEXT(A4,"000000"),'STORE LIST'!B:B,0))

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 !!")
0

## Featured Post

### Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…