Need 2 conditions to be fulfilled from large dataset

[ener]

Hello everyone,

Hope this will something simple for an Excel expert as I don't even really know where to start (Vlookup, Index Match...?

I have a large data set that I call 'Conditional Raw data lookup' for reference in my sample on the bottom.

Eg.

Condition1 12345 is valid if Condition 2 is either '111' ; '222' ; '333' ; '444 or '555'

Also
Condition1 23456 is valid if Condition 2 is either '666' ; '777' ; '888' ; '555' or '999'


On the top of my table I want an output (in Column C2 - C5) that if Condition1 12345 and Condition2 matches the raw data.

In Row2 it is 12345 and Condition 2 '111' which is maintained in the Raw data so it should say 'Yes'
In Row3 it is again 12345 but Condition 2 is '666' and this is not maintained in the Raw data so it should say 'NO'

Its a bit difficult to explain. So hopefully my sample will show it better. This is just very limited sample date. It will be much more data at the end.

Condition 1	Condition 2	Condition fulfilled?
12345	111	Yes
12345	666	No
23456	666	Yes
23456	444	No
Conditional Raw data lookup
Condition 1	Condition 2
12345	111
12345	222
12345	333
12345	444
12345	555
23456	666
23456	777
23456	888
23456	555
23456	999

<tbody>
</tbody>

Hope someone can help.

Thanks

 

[DILIPandey]

that if Condition1 12345 and Condition2 matches the raw data.

Show this match in your sample data and then what is your expected result ?


Regards,
DILIPandey

 

[ener]

Hello DILIPandey,

Thanks for getting back to me.

The table on the bottom is just raw data. Perhaps I shouldn't have named it Condition 1 / Condition 2

Just imagine Condition 1 on the bottom table is a country and Condition2 is the zip code

SO country '12345' has zip codes '111' ; '222' ; '333' ; '444' and '555'
Country '23456' has zip codes '666' ; '777' ; '888' ; '555' and '999'


On the top table is where the formula should go. So in the first row it is supposed to check if 'Country' '12345' has 'zip code' '111' and as we can see in the bottom table it does so it should output 'Yes'

the next row checks if 'Country' '12345' has 'zip code' '666' and as we can see in the bottom table it does not so it should output 'No'

And so on....

Hopefully it makes a bit more sense if you imagine 'Condition 1' to be a country (even thouh its a number) and Condition 2 is a zip code.


Btw. I also was doing a lot of searching in the web and found a workaround. Its not perfect but it would work.

I added an additional Column to the bottom table that just says '1' and I am now using the following formula: =SUM(IF(($A$11:$A$20=$A2)*($B$11:$B$20=$B2),$C$11:$C$20,"NO"))

So now it outputs in 'Condition fulfilled' either a '1' if 'Country' and 'Zip code' matches and a '0' if it doesn't.

I tried to have it output just 'Yes' or 'No' but just didn't find a way to do it.

Here is what it looks like now:


Condition 1 Condition 2 Condition fulfilled?
12345 ----------111 --------------1 (Determined by formula)
12345 ----------666 --------------0 (Determined by formula)
23456 ----------666 --------------1 (Determined by formula)
23456 ----------444 --------------0 (Determined by formula)


Conditional Raw data lookup

Condition 1 Condition 2 Helper column
12345 ----------111 -------------1
12345 ----------222 -------------1
12345 ----------333 -------------1
12345 ----------444 -------------1
12345 ----------555 -------------1
23456 ----------666 -------------1
23456 ----------777 -------------1
23456 ----------888 -------------1
23456 ----------555 -------------1
23456 ----------999 -------------1


BR

 

[Peter_SSs]

Try this, copied down. Adjust the ranges in the formula to be at least as large as the lookup table.

Excel 2010 32 bit
	A	B	C
1	Condition 1	Condition 2	Condition fulfilled?
2	12345	111	Yes
3	12345	666	No
4	23456	666	Yes
5	23456	444	No
6
7
8
9	Condition 1	Condition 2
10	12345	111
11	12345	222
12	12345	333
13	12345	444
14	12345	555
15	23456	666
16	23456	777
17	23456	888
18	23456	555
19	23456	999
20
Check Match

Cell Formulas
Range	Formula
C2	=IF(COUNTIFS(A$10:A$2000,A2,B$10:B$2000,B2),"Yes","No")

If you happen to have an old version of Excel that does not have the COUNTIFS() function, then you can use this instead.
=IF(SUMPRODUCT(--(A$10:A$2000=A2),--(B$10:B$2000=B2)),"Yes","No")

 

[ener]

Hello Peter,

That works perfectly . Looks so simple once you know how to do it.

Just a quick follow-up question for you / anyone else. Can I copy an excel spreadsheet in this forum to a table like you did above? I tried copy/paste but only comes up as text. I also inserted a table and then tried to copy my Excel sheet but it put everything into column 1. The way you have it is much better and easier to see. I also understand you can link to your excel file (drop box) etc. but a lot of people don't seem to like it as you could get viruses etc.

Thanks again for your help.

 

[Peter_SSs]

Looks so simple once you know how to do it.

Isn't that always the way?

Can I copy an excel spreadsheet in this forum to a table like you did above?

Yes, read my signature block below.