Vlookup

[ekhawaja]

Hello,

Looking for a formula that looks for an item in sheet 2, the tag name is repeated in sheet 2, and want the formula to return "yes" only if all the corresponding Status is "yes". If a "no" or "blank" or a combination of "yes", "no", "blank" exists, the formula should return "No".
Thanks in advance!

Sheet 1			Sheet 2
Tag	Status		Tag	Status
apple	no		peach	yes
orange	yes		peach	no
peach	no		apple	yes
			orange	yes
			peach	yes
			peach	yes
			orange	yes
			orange	yes
			apple
			apple
			apple	yes
			apple	no

 

[ekhawaja]

this formula is working now, error was that some of the cells had "extra space" in them which resulted in the error. I had to manually delete the "spaces" then the formula worked perfectly!!

I wanted to know if there is a possibility of extending this formula to check among 3 sheets, so Sheet 1 is where the formula will be, and it will check across Sheet 2,3,4 for the result.

Right now i am individually running the formula for each sheet.

Thanks in advance

 

[etaf]

you are welcome

 

[ekhawaja]

you are welcome

not sure if you read this:

I wanted to know if there is a possibility of extending this formula to check among 3 sheets, so Sheet 1 is where the formula will be, and it will check across Sheet 2,3,4 for the result.

Right now i am individually running the formula for each sheet.

Thanks in advance

 

[etaf]

ok,
so we need to lookup and see if the value exists in sheet2 - like we did with pear

=IF(COUNTIF(Sheet2!A2:A13,Sheet1!A2)=0,"error",IF(COUNTIF(Sheet2!$A$2:$A$13,Sheet1!A2)=COUNTIFS(Sheet2!$A$2:$A$13,Sheet1!A2,Sheet2!$B$2:$B$13,"yes"),"Yes","no"))

so instead of error , you know what to lookup and see if it is sheet 3 and if not sheet4

will the value ONLY appear in 1 sheet at any time

 

[ekhawaja]

ok,
so we need to lookup and see if the value exists in sheet2 - like we did with pear

=IF(COUNTIF(Sheet2!A2:A13,Sheet1!A2)=0,"error",IF(COUNTIF(Sheet2!$A$2:$A$13,Sheet1!A2)=COUNTIFS(Sheet2!$A$2:$A$13,Sheet1!A2,Sheet2!$B$2:$B$13,"yes"),"Yes","no"))

so instead of error , you know what to lookup and see if it is sheet 3 and if not sheet4

will the value ONLY appear in 1 sheet at any time

Thank you.

There is no reference to sheet 3 or sheet 4 in the formula though?

 

[etaf]

There is no reference to sheet 3 or sheet 4 in the formula though?

No,
i'm trying to understand what you are after

hence question

will the value ONLY appear in 1 sheet at any time

perhaps an example of the the additional sheets and results expected

as i posted - using xl2bb and a sample with expected rsults

 

[ekhawaja]

No,
i'm trying to understand what you are after

hence question

perhaps an example of the the additional sheets and results expected

as i posted - using xl2bb and a sample with expected rsults

correct, the lookup value should exist only in one of the sheets (only by mistake it would end up in multiple).

 

[etaf]

does this work for you
will only work for values that appear in 1 sheet

=IF(COUNTIF(Sheet2!$A$2:$A$13,Sheet1!A2)>0,IF(COUNTIF(Sheet2!$A$2:$A$13,Sheet1!A2)=COUNTIFS(Sheet2!$A$2:$A$13,Sheet1!A2,Sheet2!$B$2:$B$13,"yes"),"Yes","no"),
IF(COUNTIF(Sheet3!$A$2:$A$13,Sheet1!A2)>0,IF(COUNTIF(Sheet3!$A$2:$A$13,Sheet1!A2)=COUNTIFS(Sheet3!$A$2:$A$13,Sheet1!A2,Sheet3!$B$2:$B$13,"yes"),"Yes","no"),
IF(COUNTIF(Sheet4!$A$2:$A$13,Sheet1!A2)>0,IF(COUNTIF(Sheet4!$A$2:$A$13,Sheet1!A2)=COUNTIFS(Sheet4!$A$2:$A$13,Sheet1!A2,Sheet4!$B$2:$B$13,"yes"),"Yes","no"),"error")))

Book6
	A	B	C
1	Tag	Status	multisheet
2	apple	no	no
3	orange	yes	Yes
4	peach	no	Yes
5	fred		Yes
6	john		error
7			error
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(COUNTIF(Sheet2!$A$2:$A$13,Sheet1!A2)>0,IF(COUNTIF(Sheet2!$A$2:$A$13,Sheet1!A2)=COUNTIFS(Sheet2!$A$2:$A$13,Sheet1!A2,Sheet2!$B$2:$B$13,"yes"),"Yes","no"), IF(COUNTIF(Sheet3!$A$2:$A$13,Sheet1!A2)>0,IF(COUNTIF(Sheet3!$A$2:$A$13,Sheet1!A2)=COUNTIFS(Sheet3!$A$2:$A$13,Sheet1!A2,Sheet3!$B$2:$B$13,"yes"),"Yes","no"), IF(COUNTIF(Sheet4!$A$2:$A$13,Sheet1!A2)>0,IF(COUNTIF(Sheet4!$A$2:$A$13,Sheet1!A2)=COUNTIFS(Sheet4!$A$2:$A$13,Sheet1!A2,Sheet4!$B$2:$B$13,"yes"),"Yes","no"),"error")))

Book6
	A	B
1	Tag	Status
2	peach	yes
3
4
5
6
7
8	orange	yes
9	orange	yes
10	apple
11	apple
12	apple	yes
13	apple	no
Sheet2

Book6
	A	B
1	peach	no
2	apple	yes
3	orange	yes
4	peach	no
5	peach	yes
Sheet3

Book6
	A	B
1
2	fred	yes
3	fred	yes
4
Sheet4

i only keep on dropbox for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com