Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-06
9
Medium Priority
?
250 Views
Last Modified: 2012-05-12
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
Comment
Question by:Mike Miller
  • 4
  • 3
  • 2
9 Comments
 
LVL 2

Expert Comment

by:gustajimenez
ID: 36925981
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

by:Mike Miller
ID: 36926013
Is there a way to specify an undetermined amount of rows, (instead of 10?)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36926017
To do it without flipping the columns:

=index(sheet2!a:a,match(a2,sheet2!b:b,0),1)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Expert Comment

by:gustajimenez
ID: 36926039
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

by:Mike Miller
ID: 36926203
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

by:gustajimenez
ID: 36926257
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

by:Mike Miller
ID: 36926656
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

by:gustajimenez
ID: 36928830
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 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36930585
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question