Solved

Excel cell ref of matching data on two sheets

Posted on 2011-02-14
6
862 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
  • 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 29

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 29

Expert Comment

by:gowflow
ID: 34896337
Your welcome anytime
gowflow
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now