Solved

Excel cell ref of matching data on two sheets

Posted on 2011-02-14
6
873 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 30

Accepted Solution

by:
gowflow earned 500 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 30

Expert Comment

by:gowflow
ID: 34896337
Your welcome anytime
gowflow
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

728 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