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

 

[etaf]

how about
=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")

sheet1

Book2
	A	B	C
1	Tag	Status
2	apple	no	no
3	orange	yes	Yes
4	peach	no	no
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=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")

sheet2

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

 

[ekhawaja]

thank you so much for the reply @etaf , for some reason, I am getting all "yes", I feel something is wrong with my formula .

 

[ekhawaja]

thank you so much for the reply @etaf , for some reason, I am getting all "yes", I feel something is wrong with my formula .

One more thing i realized we need to incorporate, if for example we lookup "pear" in Sheet 2, and it doesn't exist in sheet 2, to return error.

 

[etaf]

for some reason, I am getting all "yes", I feel something is wrong with my formula

it would be worth posting your data layout , using xl2bb or on a share see below

f for example we lookup "pear" in Sheet 2, and it doesn't exist in sheet 2, to return error.

=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"))

Book4
	A	B	C
1	Tag	Status
2	apple	no	no
3	orange	yes	Yes
4	peach	no	no
5	pear		error
6	salmon		error
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=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"))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[ekhawaja]

I think something is wrong with the format of the table that i am using, i have 1000s rows of data, so i cannot upload sheet. Plus, i downloaded the XL2BB but the Minisheet is grayed out.

 

[etaf]

you cannot upload excel here But you could put on a share like dropbox or onedrive or google sheet
and then post the share link

 

[Fluff]

i downloaded the XL2BB but the Minisheet is grayed out.

Have a look here XL2BB Icons greyed out

 

[ekhawaja]

it would be worth posting your data layout , using xl2bb or on a share see below



=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"))

Book4
	A	B	C
1	Tag	Status
2	apple	no	no
3	orange	yes	Yes
4	peach	no	no
5	pear		error
6	salmon		error
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=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"))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

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!!

 

[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!!

thank you so much for the help @Fluff @etaf