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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top