Lookup into range

**Feastie** · 10-21-2011, 07:52 AM

Hi,
I would like to compare two product plans to see which items on plan A appear on plan B.
Essentially this would be a lookup but, instead of looking into a single column, it could be anywhere in an array.
I can do a simple FIND for each item, but there are many hundreds to search for.
I attach a sample of the two plans and how the results would look. The result could say a simple 'match' or 'no match' or, if it is possible, the col and row ref of the location in the second plan.
Thanks.

**NBVC** · 10-21-2011, 07:57 AM

With the sample you show, it seems that you are comparing cell to cell? i.e. =B4=O4 copied down and across. If not, can you elaborate a bit?

**Feastie** · 10-21-2011, 08:46 AM

It's not quite that simple as one to one, it's one to many, as the item in one position on plan A may be present on plan B but in a different location.

**Feastie** · 10-21-2011, 08:51 AM

Hi,
I have found a way around it using countif - As in Countif('range for plan B','first item in plan A') if it shows 1 it's in, otherwise it's not.
I can work it from this but, just out of interest, is there a more suitable way of doing this?

**NBVC** · 10-21-2011, 08:52 AM

So you want to check if B4 value is found anywhere within O4 to Y15?

**NBVC** · 10-21-2011, 08:54 AM

Yes, if you answered yes to my question, I would have recommended the COUNTIF to. It is the best and quickest way. You can always wrap and IF around it if you need certain text as a result.

**veejar** · 10-21-2011, 09:02 AM

Hi ,

You can also try conditional formatting, which would be probably be more quicker and easier to identify as well

Please see attached file for better understanding.

In Plan B I have used conditional formatting to match same cells in plan A and which ever match are highlighted in Green

Regards,

Veejar

**Feastie** · 10-21-2011, 09:11 AM

veejar - Unfortunately that would only work if the items were in the same position on both plans, which they aren't always, unless I'm not understanding what you have done.
NBVC - I have used a bit of IF nesting to spruce it up thanks. In my initial post I asked if there would be a way of inserting the plan refs instead of just a YES/NO, any ideas?

Regs, Feastie

**NBVC** · 10-21-2011, 09:18 AM

If you can use conditional formatting as suggested by veejar, then you can use COUNTIF too and that would be neater than getting a formula to return the address, especially if there is a possibility of duplicates.

e.g.

use conditional format formula in B4:L15

=COUNTIF($O$4:$Y$15=B4)>0

**Feastie** · 10-21-2011, 09:22 AM

NBVC - There shouldn't be any duplicates, but it is a possibility.
I can certainly work with these suggestions, I particularly like the combo of conditional formatting wth the COUNTIF.
Many thanks for both of your help.

**veejar** · 10-21-2011, 10:39 AM

Hi Feastie,

Well Thats True it would work only if the value is in the position. But I was just suggesting another way to do it. Hope you got the solution

Regards,

Veejar