Complex. Function to return value of data in a comlumns based on two cells of information

[Nic_]

I need a function that will return a value based on two main cells of information. This information then expands across a table and will contain either the info from one or both of the main cells or perhaps other info. I want to return a value in a cell that says whether it contains only information from the two cells. excel forum question.png

[azumi]

better if you upload your sample file at the forum and explain your desired results from yaour data table, to upload click "Go advanced" button and then find paperclip image button to attach the file.

regards

[FlameRetired]

Withdrawn by FR

[Nic_]

Sorry I might not have been clear enough.
The data is not meant to be symmetrical, it could come up in any order, all "dogs"except for one "no".

What I want to retrieve is a value if all the data to the right of C up to L is of the same content as cells B and C for the corresponding row.

Ie. row 3: cells D:L contain only dog and no so Id like it to return a value of "x"
Whilst row 4 cells D:L contain cat no and dog so I'd like it to return a value of "y"

IF row 4 cells D:L contained only cat and no then I'd to have a value of "x" returned.

Is that clearer?

[FlameRetired]

Yes. I had another look and determined that was the case. I didn't withdraw my post in time. Apologies.

Try this in M3 and fill down.

=IF(SUMPRODUCT(($B3=$D3:$L3)+($C3=$D3:$L3))=COUNTA($D3:$L3),"x","y")

Please Login or Register  to view this content.

Oh BTW "Dog" in D3 had a trailing space. It has to be deleted or this formula won't work.

[Nic_]

result of formula.png

Unfortunately that didn't work.

Thank you though, do you have any other suggestions?

[FlameRetired]

Yes. Try this modified version.

=IF((SUMPRODUCT(--EXACT(B3,D3:L3))>0)*(SUMPRODUCT(--EXACT(C3,D3:L3))>0)*SUMPRODUCT(EXACT(B3,D3:L3)+EXACT(C3,D3:L3))=COUNTA($D3:$L3),"x","y")

Did you read about the trailing space in my edited post #5?

[samba_ravi]

m3=IF(COUNTA(D3:L3),IF(SUM(COUNTIF(D3:L3,B3:C3))=COUNTA(D3:L3),"Y","N"),"")
Try this array formula and copy towards down (Confirm with Shift+Ctrl+Enter)

[samba_ravi]

You have a leading space / Extra Space in D3 pls remove it

[Nic_]

That worked great on your version of the spreadsheet but didn't transpose well onto mine.

It also didn't work when I added the extra "{}" at the start and end.

Does the version of excel make a difference? I am using 13

NFL Sales formula on my spreadsheet D3 has been corrected.png

NFL Sales spreadsheet.png

Thank you for your time and help it's much appreciated.

[samba_ravi]

Don't add "{}" manually
Stay in Cell and Press F2
Then Hold Ctrl and Shift keys and press enter (Ctrl+Shift+Enter) then {} will appear automatically in the Beginning and ending for the formula

[Nic_]

Hi Flame retired,

I did fix up that error.

Your formula worked well except for when all values were either "No" or "Dog" for example.

Which unfortunately they often will be.

Is there anything else that will work?

Thank you so much.

Flame Retired new formula.png

[Nic_]

That seems to have fixed it up!

Thanks heaps,

Hopefully it all runs smoothly with the thousands of rows of actual data I need it for.

Regards,

Nic