Looking up values based on two criteria

[LauriS]

I've looked through the posts and tried several suggestions but I can't seem
to get it right - and can't figure out why it's not working.

My data is on two sheets, both contain the following columns: Org, Area,
Name. One sheet is the most recent listing. The other sheet is the old
stuff.

I need to compare the new listing to the old and find any changes. Changes
could be a brand new Org/Area or a name change.

New listing example:

Org Area Name
0 0 Headquarters
0 1 Corporate
0 2 South Campus
0 3 North Campus

Old listing example:

Org Area Name
0 0 Main Office
0 1 Corporate Facility
0 3 North Campus

So there are 2 changes and one new record.

I created a column in the New listing sheet called Old Name and used the
following formula:

=LOOKUP(2,1/(($A$2:$A$26=Old!$A2)*($B$2:$B$26=Old!$B2)),Old!$C$2:Old!$C$26)

Did the CTRL/SHIFT/ENTER to make sure it was set right. Then I copied the
formula down to the other 3 cells.

My results were:

Old Name
0
Corporate Facility
0
0

I don't understand why it worked for ONE row and not any of the others??

Help?

 

[LauriS]

Ok, I found one answer. When I changed the formula to use just the rows that
had data, I got better results. But still not 100% correct.

I first added a 999 row to the Old listing to make it have the same number
of rows as the new.

That gave me the following in my Old Name column:

Old Name
Main Office
Corporate Facility
999
#n/a

I'm guessing the Lookup won't work because of the missing data between lists??

Lauri S.

 

[Max]

.. But still not 100% correct.

Try this alternative ..

In the "New" sheet,

Put in D2, array-entered (CSE):
=INDEX(Old!$C$2:$C$100,MATCH(1,(Old!$A$2:$A$100=A2)*(Old!$B$2:$B$100=B2),0))
Copy down. Adapt the ranges to suit.

 

[LauriS]

Thanks, Max!! I had tried that and it didn't work but yours did. After
checking mine against yours I found I had used the wrong sheet name in the
Index function.

I appreciate the help!

Lauri S.

 

[Max]

welcome, Lauri.
glad it helped get you going

 

[LauriS]

Ok, another problem.

I incorporated an IFNA into the formula so I could put NONE in there if
necessary.

It works fine - up until row 1002.

Here's the formula in row 1001:

=IF(ISNA(INDEX($T$2:$T$999,MATCH(1,(A1001=$R$2:$R$9999)*(B1001=$S$2:$S$9999),0))),"NONE",INDEX($T$2:$T$999,MATCH(1,(A1001=$R$2:$R$9999)*(B1001=$S$2:$S$9999),0)))

I get the correct answer. Here's the formula in row 1002:

=IF(ISNA(INDEX($T$2:$T$999,MATCH(1,(A1002=$R$2:$R$9999)*(B1002=$S$2:$S$9999),0))),"NONE",INDEX($T$2:$T$999,MATCH(1,(A1002=$R$2:$R$9999)*(B1002=$S$2:$S$9999),0)))

I get #REF! and I have no idea why!

Lauri

 

[Max]

.. I get #REF! and I have no idea why!

Think your T range is out of sync. Perhaps just a typo?
It should be: $T$2:$T$9999
for consistency with the rest of the ranges
(All ranges must be identically sized)
It should work fine once you correct it.

For info, the IF(ISNA(..) .. trap need only to trap the MATCH returns,
so you could simplify it to something like this:
=IF(ISNA(MATCH(...)),"NONE",INDEX(...,MATCH(...)))

 

[LauriS]

Thanks, Max - it was a typo!!

And thanks for the tip on the IFNA. I think that will make it easier.

Lauri S

 

[Max]

welcome, Lauri.