Multiple criteria lookup

Skully

New Member
Joined
Aug 3, 2006
Messages
9
I've searched through the past posts and can't figure this one out. By the information in the posts this one should be easy for this board.

What I'm trying to do:
I'm comparing one inventory spreadsheet with another and want to return either 'dead' or 'exception'.

On Sheet 1 I need to look up the location in column A and the part number in column B if there is a match on Sheet 2 return 'exception' and if not return 'dead'

Example data:
Location Part Number Exception?
65214 13220112 Formula Here
55412 78884221
12445 33654156

Thanks in advance for the help.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

=IF(ISNUMBER(MATCH(A2&"@"&B2,Sheet2$A$2:$A$100&"@"&Sheet2!$B$2:$B$100,0)),"exception","dead")

where A2 and B2 is the first set to match

Sheet2!A2:B100 contain the table to match against.

Adjust ranges to suit.


Formula must be confirmed with Ctrl+Shift+Enter not just Enter....you'll see {} brackets around the formula if done correctly.
 

BruceyBonu$

Board Regular
Joined
Mar 6, 2006
Messages
74
example
Sheet 1 location is col a1 downwards, part no is col b1 downwards,
Sheet 2 location is col a, part no is col b,

in Sheet 1 cell c1 use ;
=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"dead",IF(ISNA(MATCH(B1,Sheet2!B:B,0)),"dead",IF(MATCH(A1,Sheet2!A:A,0)=MATCH(B1,Sheet2!B:B,0),"exception","dead")))

In laymans tems this breaks down as follows;
Part 1 - IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"dead" - looks for a match for a1 in sheet1 in sheet2, if it cant find a match for this it returns 'dead' , however if it finds a match it proceeds to ...
Part 2 - IF(ISNA(MATCH(B1,Sheet2!B:B,0)),"dead" - Does same thing for second part, if both have matched sucesfully it does a third part...
Part 3 - IF(MATCH(A1,Sheet2!A:A,0)=MATCH(B1,Sheet2!B:B,0),"exception","dead"))) - basically this ensures the match it found is in the same row for each column

:biggrin:
 

Skully

New Member
Joined
Aug 3, 2006
Messages
9

ADVERTISEMENT

It Works!

Thanks for the help with this the formula works like a charm.

I do have a couple of questions though.

First, is there a way to accomplish this with sumproduct

Second, when I recalculate the spreadsheet it takes quite a while. Is there any way to speed it up?

Again, thanks NVBC for the quality help as always!

BruceyBonu$ thanks for the explanation, I can use the logic when working other problems.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
C2 on Sheet2, copied down:

=A2&"#"&B2

C2 on Sheet2, copied down:

=ISNUMBER(MATCH(A2&"#"&B2,Sheet2!$C$2:$C$100,0))+1

Custom format the formula cell as:

[=0]"dead";[=1]"exception"
 

Skully

New Member
Joined
Aug 3, 2006
Messages
9

ADVERTISEMENT

Aladin, question for you

You are obviously much more knowledgeable than I am about what the symbols mean.

In this section I understand what the cell references are but the '&"#"&' commands I am unfamiliar with.

=A2&"#"&B2

I understand the intent and outcome but want to understand the mechanics to replicate for the future.

Thanks for the help.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
That character as well as the @ are arbitrarily chosen characters that are most unlikely to appear in your dataset.

It is used to join the column A and column B strings without blending them into something that can yield unintended results.

Say you had 123 in Column 1 and 456 in column B

if you were to simply concatenate the 2 you would get 123456, with the @ in between you get 123@456.

Now if don't use the @ and your lookup columns had a 123 in one column and a 456 in the other colum you would get and True returned as expected, but if you had one column with 1234 and another with 56, you would also get a True, even though it really is False. With the separator character, you wouldn't have bad results.

I am not the greatest at explaining these things, but I hope you understood.
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
the & symbol means AND, the "#" is the hash mark excel will ignore the " marks

so if A1 was Craog and B1 was Shippey using A1&"#"&b1 would result in

Craig#Shippey

HTH
 

Skully

New Member
Joined
Aug 3, 2006
Messages
9
Oh, I get it

Thanks all!

Although the initial formula did work, my laptop would come to a grinding halt when I opened the spreadsheet.

The suggestion from Aladin worked to ease the load on the memory. It does add more columns but it will be easier for others to use too.

A note for the board:

With all of the support and knowledge on the problems posted there is just no way that you can survive without learning. This has been very helpful!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,162
Members
410,667
Latest member
Gaexel
Top