Returns value if...

[tonyrensya]

Hello all,

I'm using Excel 2019, here is my problem to be solve:
If value is A1 or A2, returns 1
If value is B1 or B2, returns 0

Data	value
Tom	A1
John	B1
Mary	A2
May	B2

Outcome	Returns
Mary	1
Tom	1
May	0
John	0

Great thanks to all

 

[SanjayGMusafir]

If value is A1 or A2, returns 1
If value is B1 or B2, returns 0

And, how or from where are you getting this value A1 etc...

Also, if you are using Excel 2019 update same in your profile too so that others are able to see and deliver solution accordingly.

 

[FormR]

Hi, like this maybe:

Book1
	A	B
1	Data	value
2	Tom	A1
3	John	B1
4	Mary	A2
5	May	B2
6
7	Outcome	Returns
8	Mary	1
9	Tom	1
10	May	0
11	John	0
Sheet1

Cell Formulas
Range	Formula
B8:B11	B8	=0+OR(VLOOKUP(A8,$A$2:$B$5,2,0)={"A1","A2"})

 

[tonyrensya]

Thank you very much, it works as it suppose.
If there's no value be found in vlookup, could it be the cell become blank instead of 0 ?

 

[tonyrensya]

And, how or from where are you getting this value A1 etc...

Also, if you are using Excel 2019 update same in your profile too so that others are able to see and deliver solution accordingly.

the value "A1" ,"B1" is just a text, I'm sorry for misleading.

Thank you for reminding. I've changed my profile as well

 

[FormR]

If there's no value be found in vlookup, could it be the cell become blank instead of 0 ?

Do you mean when a match can not be found in the "data" volume or when a match can be found but the "value" column is blank?

 

[tonyrensya]

Do you mean when a match can not be found in the "data" volume or when a match can be found but the "value" column is blank?

I mean if the "value" column is blank, then the "returns" column shows blank instead of 0
Thank you very much!

 

[FormR]

Hi, you could try like this:

Book1
	A	B
1	Data	value
2	Tom	A1
3	John	B1
4	Mary	A2
5	Fred
6	May	B2
7
8	Outcome	Returns
9	Mary	1
10	Tom	1
11	May	0
12	Fred
13	John	0
Sheet1

Cell Formulas
Range	Formula
B9:B13	B9	=IF(VLOOKUP(A9,$A$2:$B$6,2,0)="","",0+OR(VLOOKUP(A9,$A$2:$B$6,2,0)={"A1","A2"}))

 

[tonyrensya]

Hi, you could try like this:

Book1
	A	B
1	Data	value
2	Tom	A1
3	John	B1
4	Mary	A2
5	Fred
6	May	B2
7
8	Outcome	Returns
9	Mary	1
10	Tom	1
11	May	0
12	Fred
13	John	0
Sheet1

Cell Formulas
Range	Formula
B9:B13	B9	=IF(VLOOKUP(A9,$A$2:$B$6,2,0)="","",0+OR(VLOOKUP(A9,$A$2:$B$6,2,0)={"A1","A2"}))

It only returns 1 if the value is A1, but not A2, its there anything wrong?

 

[FormR]

It only returns 1 if the value is A1, but not A2, its there anything wrong?

In post#8, the first row of the results table contains "Mary" whose value in the data table is "A2" and the formula returns 1 as expected - perhaps you can post some sample data that demonstrates the problem?