Comparing two ranges and returning a match

Hey there,

I need a function to compare two ranges and return a match. For example

Range A: {A56, B43, C98, D44}
Range B: {A33, G89, D44, K123, OB55}

the function would return D44. Is it possible at all with functions or do I need a macro?

Thank you in advance!
-MachtiSonni

Originally Posted by teelim

macro not required.

hope this helps

Thank you, but not quite what I'm looking for. I need to compare the whole two ranges to each other. Not just one value. If ANY of the cells in range A matches ANY of the cells in range B.

I guess I could do that with multiple nested IF-functions.

Here, try this:

=INDEX(A:A,MATCH(FALSE(),ISNA(MATCH(A:A,B:B,0)),0))

comfirmed with ctrl+shift+enter

This will return you first value that match or #N/A if there is no match

Originally Posted by teelim

it does compare the whole range.

put your data in Col A and Col B, then copy and drag the formula in Col C to the bottom (to the last data at Col A)

all the data that match will show up in Col C

Oh, true. Didn't get that. Cheers!

Originally Posted by zbor

Here, try this:

=INDEX(A:A,MATCH(FALSE(),ISNA(MATCH(A:A,B:B,0)),0))

comfirmed with ctrl+shift+enter

This will return you first value that match or #N/A if there is no match

Ummm...I can't get it to work. It returns #N/A even if I definately do have a match at the ranges. And yes, I used ctrl-shift-enter for an array formula...

I don't know why.. Look here:

Book1(1).xls

Cell D1 returns first
Cell E1 count how many there is matching values

Originally Posted by zbor

I don't know why.. Look here:

Attachment 63228

Cell D1 returns first
Cell E1 count how many there is matching values

Weird...seems like an exact copy of the one I put in and now it works...But thank you very much, both of you! You've been very helpful.

Just for possible future reference, is there an easy way to return an array of all the matches here? Surely by coding, but with a function?

You can use teelim approach and in first row put FILTER and filter out blanks.

However, rather than VLOOKUP use MATCH function:

=IF(ISNA(MATCH(A1;$B:$B;0));"";A1)

and pull down

(change ; to , if needed)

Hi

Sorry to barge in on your thread, but this solved a problem I was having, but it has created another!

The formula you have created works fine in Excel 2007, but always returns #N/A in 2003. Have you any idea why this is?

We use both versions in our company as we are very large and transitioning is taking time. I run both versions on my PC and there is no conflict as far as I can see and my IT dept confirmed that the two versions will not conflict.

Thanks for any ideas on this.

Attachment I put above is saved for XL 2003 and it should work. Can you open it and check?

Hi zbor

thanks but no, I opened it in xl2003 and on the cell with the formula, pressed F2 to activate the cell, then shift+ctrl+enter as normal for array and it returned #N/A

pike - thanks for suggestion, but this is more of a head scratcher for me to understand why a striaghtforward formula works in 2007 but not 2003. I could add the code, but I'd still have that nagging thing in my head to why the formula won't work!!!

In that case I would suggest to open new thread due to rule no 2

2. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Edit: try to put ranges A1:A1000 instead of A:A (for all ranges)

Originally Posted by zbor

I don't know why.. Look here:

Attachment 63228

Cell D1 returns first
Cell E1 count how many there is matching values

Okay,

now I really started using this. I found out the weirdest error. When I open up the attachment it works allright. But when I change anything and I mean anything at all it starts giving me #N/A. For example if I add "G43" to cell A11 it gives me #N/A to cell D1...