?
Solved

Excel cell ref of matching data on two sheets

Posted on 2011-02-14
6
Medium Priority
?
874 Views
Last Modified: 2012-05-11
Hi,
Please refer attached XL file.
I need to findout the matching Names of Sheet1 and Sheet2 and to show the matching 'Cell Ref' of Sheet2 in Sheet1 column C. Also I need to update the matching School Name from Sheet2 to Sheet1 column D.
Thank you.   Test.xlsx
0
Comment
Question by:sarans1961
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:PSSUser
ID: 34895365
Hi Sarans,

is putting an extra column onto sheet 2, to combine first and last name, an option?

Trying to retrieve values based on a 2 column lookup is tricky.

If you can insert a column to the left of all the data (i.e. column A) you can use VLOOKUP to retrieve the school name.

If the extra column can't be inserted before the data, but could be added to column D, the row containing the full name can be returned using Match. This can then be used to create a cell reference and return the school name.

If adding an extra column to the second sheet isn't an option I'll have a think to see if I can find a way to do this. Do you mind a Macro or UDF solution, which can be slow, or would you prefer a native Excel one?

Thanks
Chris
0
 

Author Comment

by:sarans1961
ID: 34895555
Hi Chris,

Thank you for your comments.

I need native Excel one.

Please post a file with your solution that will help me to complete further.

Best regards,

Sarans  
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 34895871
Is this what ur looking for ?
I'v combined in sheet2 Col X (we can push if necessary) first+last to ease search.
Sorry but the file is a bit big 7MB u'll hv to be patient ur original was 4.5MB.

Pls let me know if any change needed. We can also do it by VBA abviously That I usually favor more as your not compelled to re-insert and manipulate formulas in case your file grows. !!!

Anyway chk it and let me know.

PS the file is too big and this site is damn slow Iv been waiting now 1hour to upload the file and it is still not finished. Pls do the following
in Cell C2 of Sheet1 put the following formula:
=IF(ISNA(ADDRESS(MATCH(A2&B2,Sheet2!$X$2:$X$172020,0),1))=FALSE,ADDRESS(MATCH(A2&B2,Sheet2!$X$2:$X$172020,0),1) & ADDRESS(MATCH(A2&B2,Sheet2!$X$2:$X$172020,0),2),"Not Found")

in Cell D2 of Sheet1 put the following formulas
=IF(C2<>"Not found",INDEX(Sheet2!$A$2:$X$172020,MATCH(A2&B2,Sheet2!$X$2:$X$172020,0),3),C2)

in Cell X2 of Sheet 2 put the following formulas:
=A2&B2

Drag in Sheet2 the formulas created to the end of the data file till Cell X172020
Doo the same for sheet1 drag the formulas to the end of data in sheet1 ie row 300

Ity should do it for you if u cannot uplpad the file.
Rgds/gowflow
Test.xlsx
0
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!

 

Author Comment

by:sarans1961
ID: 34896253
Hi Gowflow,

You are great!

Thank you very much for your help and the support.

Best regards,

Sarana
0
 

Author Closing Comment

by:sarans1961
ID: 34896265
Perfect solution. Thanks for the great support.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 34896337
Your welcome anytime
gowflow
0

Featured Post

Industry Leaders: 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

777 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