Cross-reference cell contents from multiple cells, based on previous cross-reference

[jekkis]

Hi,


I'm relatively new to Excel so please apologize me if the question is rather trivial to you.

To give some context, assume I have two sets of data, something like this:

Data 1:

NAME	AGE
JOHN	29
JACK	36

Data 2:

NAME	AGE
JOHN	35
JACK	29

Now from those two sets of data, I want to cross-reference if the name "JOHN" is found on both of them. I've chosen to approach this problem with the following function:

=NOT(ISERROR(MATCH(A2; $I$2:$I$6500; 0)))

Which gives me the output if "JOHN" is found within I2-I6500. My problem is in the next step: I want to cross-reference that I find a match for both values, name and age, and the latter must be based on the same row where the first match was found. So, if I find match for name "JOHN" let's say in cell I500, I want my function to look for match for the age value in row 500 as well (that'd most likely be cell J500, then).

If I just copy the function to apply for values in other cells, that will just lead it to matching any age found in the range I'm using, in this case it would then match the age value from "JACK" to what I'm looking to find out for "JOHN", so that won't obviously work.

Any suggestions what would be the best way to approach this problem? I'm sure there's a solution where I can use one function and apply it to cover multiple cells, but just don't find an angle for this...

Thanks a lot!

[alansidman]

I would do it with Power Query where I have two files Data and Data(2) with names and ages

Here is a sample Mcode

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.


- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.