Link to home
Create AccountLog in
Avatar of sarans1961
sarans1961Flag for United Arab Emirates

asked on

Excel cell ref of matching data on two sheets

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
Avatar of PSSUser
PSSUser
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of sarans1961

ASKER

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  
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi Gowflow,

You are great!

Thank you very much for your help and the support.

Best regards,

Sarana
Perfect solution. Thanks for the great support.
Your welcome anytime
gowflow