Solved

Excel cell ref of matching data on two sheets

Posted on 2011-02-14
6
871 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

809 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