If match then display 0 or 1

[pateln1594]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]FULL ID[/TD]
[TD]LEFT ID[/TD]
[TD]RIGHT ID[/TD]
[TD]TYPE[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD][/TD]
[TD]1234[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD]2345[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]3456[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6234[/TD]
[TD][/TD]
[TD][/TD]
[TD]6234[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4657[/TD]
[TD][/TD]
[TD][/TD]
[TD]4657[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6879[/TD]
[TD][/TD]
[TD][/TD]
[TD]6879[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1243[/TD]
[TD][/TD]
[TD]1243[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5436[/TD]
[TD]5436[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7896[/TD]
[TD][/TD]
[TD]7896[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4894[/TD]
[TD]4894[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


I am having trouble getting the values in Column "Type".
So the idea here is that I have a list of ID's for my device. Some of my devices are full devices, whereas some are only right or left devices. I want the type column to display 0 if they are full devices and display 1 if they are right or left devices. I want to do this using an excel formula not VBA.

I was trying to use the MATCH function to compare the ID column with full right and left columns and then if its a match result is 0 or 1 but I was not able to get the formula correct. Please help!!

 

[Joe4]

Is your data representative of all the possibilities you have, namely that:
- exactly one of Full ID, Left ID, Right ID will be populated per row
- the value filled out in Full ID, Left ID, Right ID will always match the ID

If those are not always true, can you post a few of the other examples, and expected results?

If those two conditions are always true, you could just use (assuming data resides in columns A-D and starts on row 2):

=IF(A2=B2,0,1)

 

[jtakw]

Hi,

Same assumption as Joe4, just reversed the logic for the formula:

Book1
	A	B	C	D	E
1	ID	FULL ID	LEFT ID	RIGHT ID	TYPE
2	1234		1234		1
3	2345	2345			0
4	3456	3456			0
5	6234			6234	1
6	4657			4657	1
7	6879			6879	1
8	1243		1243		1
9	5436	5436			0
10	7896		7896		1
11	4894	4894			0
Sheet164

Cell Formulas
Range	Formula
E2	=--(A2<>B2)

Formula copied down.

 

[shg]

Another way:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

ID​

[/td][td="bgcolor:#F3F3F3"]

FULL ID​

[/td][td="bgcolor:#F3F3F3"]

LEFT ID​

[/td][td="bgcolor:#F3F3F3"]

RIGHT ID​

[/td][td="bgcolor:#F3F3F3"]

Type​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1234​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]Invalid[/td][td]E2: {=IF(COUNT(B2:D2)=1, INDEX($B$1:$D$1, MATCH(TRUE, ISNUMBER(B2:D2), 0)), "Invalid")}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

2345​

[/td][td]

1234​

[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]FULL ID[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

3456​

[/td][td][/td][td]

1234​

[/td][td][/td][td="bgcolor:#CCFFCC"]LEFT ID[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

6234​

[/td][td]

1234​

[/td][td]

1234​

[/td][td][/td][td="bgcolor:#CCFFCC"]Invalid[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

4657​

[/td][td][/td][td][/td][td]

1234​

[/td][td="bgcolor:#CCFFCC"]RIGHT ID[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

6879​

[/td][td]

1234​

[/td][td][/td][td]

6879​

[/td][td="bgcolor:#CCFFCC"]Invalid[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1243​

[/td][td][/td][td]

1243​

[/td][td]

1234​

[/td][td="bgcolor:#CCFFCC"]Invalid[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

5436​

[/td][td]

5436​

[/td][td]

1234​

[/td][td]

1234​

[/td][td="bgcolor:#CCFFCC"]Invalid[/td][td][/td][/tr]
[/table]