This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 8%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESC%3C/text%3E%3C/svg%3E)
Hello all
Here is one situation:
I have to make a check cell of comparing two columns of filtered table (red & blue columns) with its its mother data table (red & white).
And want to make decision that the two columns value from mother table is included in filtered table or not.
Example :
Mother table >
Filtered table (without zeros):
Formula req: if(VAlues of two columns from Filtered table (without zeros) in included from mother table), "OK","NOTOK")
________----------------------------------------
Filtered table is shown below.
Mother table is shown below.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Hi,
Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.
This is very informative and i understood it for two by two rows
BUT-- My requirement is for larger tables.
Please see below --
The check : OK and NOTOK shall come at the bottom as the filter table column wise at end of each columns.
Filter Table: Large Table
Mother Table (is also large)
Mother Table i
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 68%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHC%3C/text%3E%3C/svg%3E)
If that's the case, you can try this formula at the bottom of the first filtered-table column then drag it to the right:
=LET(
fRows,'Add Red column of filtered table,
fVals,'Add Data of the 0.25 blue column of filtered table,
fHead,'Add The column header 0.25 of filtered table,
mRows,'Add Red column of mother table,
mHeads,'Add The header row of mother table,
mData,'Add all the large data ranges of mother table,
mCol,XMATCH(fHead,mHeads,0),
mVals,XLOOKUP(fRows,mRows,CHOOSECOLS(mData,mCol),0),
nonZero,fVals<>0,
IF(SUM(--nonZero)=0,"NOTOK",
IF(SUMPRODUCT(--nonZero,--(mVals>=fVals))=SUM(--nonZero),"OK","NOTOK")
))
You can try this and let me know if it helps. If not, don't hesitate to reach out again and I'll be happy to assist you further. Thank you for your cooperation and understanding. Hope to hear from you soon.