Excel - Lookup and Match Values with multiple conditions

Question

Currently have the following excel table

I would like to do the following

I want to write a IF statement or something else which would do this:

if (the value in service 4 for company 1 is 1, and the value in service 1 for company 1 is also 1, then copy the value 1 to a new column)

how can one do this in excel ?

If the matching does not exist then copy 0 or nothing

It's unclear from your question, what should be the **desired / expected result**. For example, in your table, the first instance of Company1 should return 1, however the second should return 0. Is the desired result two lines, in which one is 1 and other is 0 or a congregate of those two that ends in logical `AND` or maybe even `OR`? **I voted to close the question as unclear until this is specified.** — Samuel Hulla, Jul 26 '18 at 13:05
2 seperate lines, one after the other, so first row is 1 and second row is 0 - please help — Data Science, Jul 26 '18 at 13:10
i mean if i want to match on multiple criteria, e.g. check values in one column, then check values in another column>? — Data Science, Jul 26 '18 at 13:17
@DataScience have you tried my answer? If you have the data on 1 row as you showed -- I assumed each line was an independent instance. — Solar Mike, Jul 26 '18 at 13:18
@DataScience my answer is looking at two columns for the "1" as you requested. — Solar Mike, Jul 26 '18 at 13:19
will repost for more clarity - i think its more complicated please see new post — Data Science, Jul 26 '18 at 13:20
Possible duplicate of [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) — Data Science, Jul 26 '18 at 13:20
not used to people flagging their own question as a possible duplicate! — Forward Ed, Jul 26 '18 at 13:25

score 2 · Answer 1 · answered Jul 26 '18 at 13:02

2

How about:

=IF(AND(B2=1,E2=1)=TRUE,1,0)

as based on :

Or you could use:

=IF(SUM(B2,E2)=2,1,0)

which achieves the same thing.

answered Jul 26 '18 at 13:02

Solar Mike

7,156
4
17
32

you don't need the =True part. And(B2-1,E2=1) is enough – Forward Ed Jul 26 '18 at 13:06
While this is technically a functional answer, I do kind of suspect that the data OP provided here was just an example and you have to account for the fact that the data can contain more than a single row per company (hence even in his example, one instance of company 1 shoulde result in true and one in false (1) and (0). It's not really a knock against your answer, but more of lacking details in the original question – Samuel Hulla Jul 26 '18 at 13:06
@ForwardEd, good point and quite right, but I did it to make it clear for the OP. – Solar Mike Jul 26 '18 at 13:15
Basically, your answer should be fine. Here is some shorthand: =AND(B2=1,E2=1)*1 or to make it unclear what is going on =(B2=1)*(E2=1)*1 – FocusWiz Jul 26 '18 at 13:21
I take that back since any answer is dependent on what the new version of the question becomes. – FocusWiz Jul 26 '18 at 13:37

score 0 · Answer 2 · answered Jul 26 '18 at 13:18

0

Assuming you put the company name that you want to check in G1 you could use the following

=--AND(VLOOKUP(G1,$A$2:$E$5,2,0)=1,VLOOKUP(G1,$A$2:$E$5,5,0)=1)

For you results of just needing 1 or 0 the above would work as true will evaluate to 1 and false will evaluate to 0.

However if you wanted some other result other than 1 and 2 I would change the formula to:

=IF(AND(VLOOKUP(G1,$A$2:$E$5,2,0)=1,VLOOKUP(G1,$A$2:$E$5,5,0)=1),"True Result","False Result")

answered Jul 26 '18 at 13:18

Forward Ed

9,484
3
22
52

Hi Ed, I get the feeling we are trying to answer a question that is about to be changed as per the OP's comments... – Solar Mike Jul 26 '18 at 13:20

Excel - Lookup and Match Values with multiple conditions

2 Answers2