Excel cell ref of matching data on two sheets

Posted on 2011-02-14
Last Modified: 2012-05-11
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
Question by:sarans1961
  • 3
  • 2
LVL 10

Expert Comment

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?


Author Comment

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,

LVL 29

Accepted Solution

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:

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.
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.


Author Comment

ID: 34896253
Hi Gowflow,

You are great!

Thank you very much for your help and the support.

Best regards,


Author Closing Comment

ID: 34896265
Perfect solution. Thanks for the great support.
LVL 29

Expert Comment

ID: 34896337
Your welcome anytime

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 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