Formula to find and retrieve a specific value from a list with multiple values

emka

Hi Community,

I am not sure what formula or combination of formulas to use for the below example:

I have a list of Spring products, each product is sorted into a type, and a similar list for Fall products. For each product I have a column to check if that specific product has an image. In a different sheet (here I use columns TYPE 2 & FORMULA?? but those should be on a different sheet), I have my list of types and I am looking for a formula that outputs the following: Look in the Spring table to see if any pant has an image, if it has an image output yes. If none of the Spring pants have images, look to see if any Fall pants have images. If any have an image, output yes, if none have images, output no.

Is there anything that I can use for that? Thanks in advance!

Kelly Moore

Hey @emka

Try this:

=IF(OR(COUNTIFS({Source sheet Spring Type column}, Type@row, {Source Sheet Spring Image column}, "Yes") > 0, COUNTIFS({Source Sheet Fall Type}, Type@row, {Source Sheet Fall Image column}, "Yes") > 0), "YES", "NO")

In case you are unfamiliar with 'cross sheet references' you will have to physically create references - you cannot simply copy paste this formula into your sheet. If you haven't created references before, see this link

Will this work for you? If you need any help, just shout out

Kelly

Kelly Moore

@emka

I'm glad that it worked for you and even happier that you further modified it to better suit your needs.

Kelly

Kelly Moore

Hey @emka

Try this:

=IF(OR(COUNTIFS({Source sheet Spring Type column}, Type@row, {Source Sheet Spring Image column}, "Yes") > 0, COUNTIFS({Source Sheet Fall Type}, Type@row, {Source Sheet Fall Image column}, "Yes") > 0), "YES", "NO")

In case you are unfamiliar with 'cross sheet references' you will have to physically create references - you cannot simply copy paste this formula into your sheet. If you haven't created references before, see this link

Will this work for you? If you need any help, just shout out

Kelly

emka

Hi @Kelly Moore ,

That is fantastic thank you so much! Worked like a CHARM and I was able to modify it to add 3 extra source sheets.

Kelly Moore

@emka

I'm glad that it worked for you and even happier that you further modified it to better suit your needs.

Kelly