Comparing two columns vales from filtered table and original table and decide the values are present or not.

santosh chaturvedi 0

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 > User's image

Filtered table (without zeros): User's image

Formula req: if(VAlues of two columns from Filtered table (without zeros) in included from mother table), "OK","NOTOK")

________----------------------------------------

Filtered table is shown below.

User's image

Mother table is shown below.

User's image

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-05-27T23:35:36.67+00:00

Hi,

Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.

Answer recommended by moderator

Chaturvedi, Santosh 430

Hello Greetings Hendrix > Many thanks for clarifications provided.

Moving further ..

I am searching a solution for this case.

I cannot able to attach the excel sheet as your system is not allowing it, : The image is shown below.

..............................................................

See cell i5 > it shows ( [σa ] → 0.25, 0.75,1.8... 50) and ([σm ]↓ =27.5,20.5,19.5....30.5)
I need to Filter the data in column A,B,C with following requirements.

..............................................................

In column A6 - A41 : The values of [σa ] → 0.25 shall come. ( see example is shown)
In column B6 - B41 : The values of I6-I41 shall come.
In column C6 - C41 : The values of J6-J41 shall come that shall not contain zeros. ........................................................

The same will be repeated for [σa ] → 0.75 0.75,1.8... 50,..... which will be sequenced below the table of [σa ] → 0.25, see image below.

In column A42 - A77 : The values of [σa ] → 0.75 shall come. see example is shown
In column B42 - B77 : The values of I6-I41 shall come.
In column C42 - C77 : The values of J6-J41 shall come that shall not contain zeros. ........................................................

The same will be repeated for [σa ] → 1.8...........26 located in row 5

--> (See A78, B78m C78 for message)

.......................................................

I am attaching the excel sheet for more information with markings

User's image

Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-02T22:22:12.96+00:00

Hi @Chaturvedi, Santosh,

I greatly appreciate your sharing.

I will work with the solution on my testing environment and reach out again with the best possible guidance for your concern as soon as possible.

In the meantime, I will recommend this answer to pin it to the top so other users from the forum can find your post and provide you more guidance/suggestions.

Thank you for your patience and understanding.
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-03T20:12:40.8533333+00:00
Hi @Chaturvedi, Santosh,

For your concern, you can try using this formula to achieve your request:

=LET( sa,$F$1:$Z$1, sm,$E$2:$E$37, vals,$F$2:$Z$37, r,ROWS(vals), c,COLUMNS(vals), seq,SEQUENCE(r*c), flatVals,TOCOL(vals,0,TRUE), flatSa,INDEX(sa,1,ROUNDUP(seq/r,0)), flatSm,INDEX(sm,MOD(seq-1,r)+1), keep,(flatVals<>"")*(flatVals<>0), HSTACK(FILTER(flatSa,keep),FILTER(flatSm,keep),FILTER(flatVals,keep)))

The ranges in the formula are based on my testing worksheet, so you need to modify the ranges so the formula can work properly in your data structure.

The result of my suggested formula will be like this (it filters up to the last [σa] value):

After that, you can use Conditional Formatting or manually format the table color based on your preferences.

Hope this would be a suitable solution for your concern. If you have any questions or need any further clarification, please feel free to reach out again.

Thank you for your patience and understanding. I'm looking forward to your reply.
Chaturvedi, Santosh 430 Reputation points

2026-06-04T08:56:56.1333333+00:00
Hello & Greetings Hendrix from sailing city Kiel (Germany !!)

Many thanks for solution provided. The formula with proper ranging is very well working.

I have this last two wishes and then the issues will be fixed for this case.

"Since you have formulated the formula then you can easily put these requirements in the formulation."

...............................................................................

SORTING: Max to Min values of Ni. Please see the image below. This will be applicacle to all table entries of 0.25, 0.75. 1.3, 1.8.. as shown in your feed back image.

.....................................

2: Checks: This is for quality checks that the values of Ni for 0.25, 0.75,1.3,1.8....came in table of columns from filter table is SAME or not.

Please see below.

Santosh
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-04T20:37:33.7866667+00:00
Hi @Chaturvedi, Santosh,

Please clarify me if I have misunderstood anything about your request.

1/ Formula for Sorting

There are different ways to sort based on your preference:

If you want to perform sorting while extract the filter table, you can use this formula to replace the formula given in my previous response:

=LET(sa,$F$1:$Z$1,sm,$E$2:$E$37,vals,$F$2:$Z$37,r,ROWS(vals),seq,SEQUENCE(r*COLUMNS(vals)),flatVals,TOCOL(vals,0,TRUE),flatSa,INDEX(sa,1,ROUNDUP(seq/r,0)),flatSm,INDEX(sm,MOD(seq-1,r)+1),keep,(flatVals<>"")*(flatVals<>0), fSa, FILTER(flatSa,keep), fSm,FILTER(flatSm,keep), fNi, FILTER(flatVals,keep), SORTBY(HSTACK(fSa,fSm,fNi),fSa,1,fNi,-1))

If you want to sort your existing spilled result instead of replacing the extraction formula, use this formula instead

=SORTBY(A2#,INDEX(A2#,0,1),1,INDEX(A2#,0,3),-1)

If you want to have one separate spilled table per [σa] value, you can use this formula with a helper [σa] cell (e.g. AH2). When you input the [σa] value in cell AH2, the formula will return the corresponding sorted table of that input value

=LET(src,$A$2#,k,AH2,grp,FILTER(src,INDEX(src,0,1)=k),SORTBY(grp,INDEX(grp,0,3),-1))

2/ Formula for Checking

In your first check cell, use this formula then copying it right to the last column:

=IF(SUMPRODUCT(($A$2:$A$357=F$1)*($C$2:$C$357))=SUM(F$2:F$37),"OK","NOT OK")

Note: The ranges in the formula are based on my testing worksheet, so you need to modify the ranges so the formula can work properly in your data structure.

If you need any further assistance, please don't hesitate to ask and I will be happy to help as much as possible within my abilities.

Thank you for your patience and understanding. Hope to hear from you soon.
Chaturvedi, Santosh 430 Reputation points

2026-06-05T07:07:03.78+00:00

Hello - Greeting !!

These formula is yielding the solution--

=LET(sa,$F$1:$Z$1,sm,$E$2:$E$37,vals,$F$2:$Z$37,r,ROWS(vals),seq,SEQUENCE(rCOLUMNS(vals)),flatVals,TOCOL(vals,0,TRUE),flatSa,INDEX(sa,1,ROUNDUP(seq/r,0)),flatSm,INDEX(sm,MOD(seq-1,r)+1),keep,(flatVals<>"")(flatVals<>0), fSa, FILTER(flatSa,keep), fSm,FILTER(flatSm,keep), fNi, FILTER(flatVals,keep), SORTBY(HSTACK(fSa,fSm,fNi),fSa,1,fNi,-1))

=IF(SUMPRODUCT(($A$2:$A$357=F$1)*($C$2:$C$357))=SUM(F$2:F$37),"OK","NOT OK")

It means the soluntions is made.

Many thanks for your experties!!

Santosh
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-05T15:48:28.13+00:00

You're welcome @Chaturvedi, Santosh,

I'm very glad to know that my sharing would help resolve your concern.

For any concerns or questions in the future, please don’t hesitate to reach out. Not only will I be happy to assist you, but all moderators in the Q&A forum will also do their best to provide support and guidance.

I wish you a wonderful day ahead.
Chaturvedi, Santosh 430 Reputation points

2026-06-08T06:32:19.1733333+00:00

Hello Hendrix -- Greetings !!

I want to learn more detail and advance level of formulation in Excel. Do you have or Microsoft have any link and learning course material or interactive training material on the advance excel formation.

I am mechibaical Engineering with master degree and i do have lot interest in these areas.

Please help me or share if you have any tarining material so that I can learn more in depth in this reagrds

Santosh
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-09T20:14:35.0166667+00:00
Hi @Chaturvedi, Santosh,

For advanced learning based on your background as mechanical engineering, I would recommend focusing on the following Excel formula areas:

Core formulas: XLOOKUP, INDEX, MATCH, and FILTER for advanced lookup and data extraction; IF, IFS, AND, OR, and SWITCH for logical calculations

Modern formulas like LET and LAMBDA for building cleaner

Reusable formulas SORT, UNIQUE, SEQUENCE, and other dynamic array formulas

Engineering and mathematical functions such as CONVERT, SIN, COS, TAN, POWER, SQRT, and statistical functions

These topics will be useful for engineering-related work such as material property lookup, stress calculations, unit conversion, tolerance analysis, and technical reporting. Once you are comfortable with the core formulas, you can move into more advanced analysis tools such as PivotTables, Power Query, Power Pivot.

You can start with these official Microsoft resources and practice step by step using your own engineering examples:

Excel help & learning: this is Microsoft’s official Excel learning hub, covering formulas, functions, PivotTables, data analysis, templates, troubleshooting, and other Excel features.

Excel functions (by category) | Microsoft Support: this page lists Excel functions by category, including Engineering, Math & Trigonometry, Statistical, Logical, Lookup & Reference, Financial, Date & Time, and Text functions. It is very useful for advanced formula learning.

You can also take a look at other online websites/courses (e.g Excel Courses & Certificates [2026] | Coursera) for more practice resources.

(This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.)

Hope this will be helpful for your concern. Please don't hesitate to reach out again if you need anything else in the meantime.

Many best wishes for a great day.

8 additional answers

Chaturvedi, Santosh 430 Reputation points

2026-06-14T10:48:32.96+00:00

Hello --

I have shown the requirement as shown below

I have to indrectly link the filter function located 2 work book and to the range table located in 1st workbook,

This sall happen via linking the address from 1st work book to the 2nd workbook

See below for calrifications.

Requesting to please send the copy paste formula so that i can use directly
Was this answer helpful?
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-14T22:59:00.1266667+00:00

Hi @Chaturvedi, Santosh,

Based on your information, you can try this formula to see if it works in your situation:

=LET(addr,Dash!E3,bookSheet,TEXTBEFORE(addr,"!"),rangeName,TEXTAFTER(addr,"!"),dataRange,INDIRECT("'"&bookSheet&"'!"&rangeName),checkRange,INDIRECT("'"&bookSheet&"'!$F$6:$DA$105"),FILTER(dataRange,BYROW(checkRange,LAMBDA(r,SUM(r)>0)),""))

This formula has limitation that INDIRECT cannot read an external reference if the source workbook is close and it will return #REF! error. Therefore, for formula-using approach, you need to have both of your workbooks open to use the formula.

Please try the formula and let me know if it works. If not, please don't hesitate to reach out again and I will try my best to help you.

Thank you for your patience and understanding. Hope to hear from you soon.

Chaturvedi, Santosh 430 Reputation points

2026-06-15T07:29:25.09+00:00

Hello Hendrix.

I will this SQL formula and check it..

In the mean time i have one more requirement. I am using my logic to fix the below issue. If you have better options for below issue that it will be very fine for me.

Please advice..

Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-15T19:11:48.5933333+00:00

Hi @Chaturvedi, Santosh,

In your case, instead of using a shape hyperlink, you can use a VBA macro assigned to the shape. A shape hyperlink in Excel is stored as a Hyperlink object for that shape with a SubAddress internal destination. This internal target is not treated as a relative sheet reference and cannot automatically retarget to the copied sheet when you duplicate a worksheet.

You can follow these steps:

Press Alt + F11 to open VBA editor > select Insert > Module > paste this script into it:

Sub GoToLocalG7() Application.Goto ActiveSheet.Range("G7"), True End Sub

Close the VBA editor

Go to the shape in the first sheet > remove the existing link hyperlink from the shape UPDATE ME

Then right click that shape UPDATE ME > Assign Macro > assign the macro GoToLocalG7

As a result, every copied shape still calls the same macro, and the macro will jump to G7 on whichever worksheet you click.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Chaturvedi, Santosh 430 Reputation points

2026-06-14T10:45:02.9933333+00:00

Hello -- I had tried many time biu not happening please help me again in this regard.

I have shown the requirement as shown below

I have to indrectly link the filter function located 2 work book and to the range table located in 1st workbook,

This sall happen via linking the address from 1st work book to the 2nd workbook

See below for calrifications.

Requesting to please send the copy paste formula so that i can use directly
Was this answer helpful?

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
santosh chaturvedi 0 Reputation points

2026-05-28T13:49:57.23+00:00

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
Was this answer helpful?
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-05-28T21:24:03.7066667+00:00

Hi @santosh chaturvedi,

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.

Chaturvedi, Santosh 430 Reputation points

2026-06-01T08:22:59.7366667+00:00

Hello Hendrix- Greetings.

I hope that these vauable advices are not charable from your side. I am concern that at end i have to pay from my end for the above explanations, whoes income is not so high. Please do not take otherwise, I just want to be sure.

If i can send this excel sheet then it is possible that you fix the case. ?

Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-01T22:49:34.5066667+00:00

Hi @Chaturvedi, Santosh,

The Microsoft Q&A forum is a place for users can freely ask questions, share experiences and advice that relates to Microsoft 365 topics. Therefore, your questions and advice aren't charged here.

If possible, you can share the link of your sample sheet here so not only I, but also other moderators can see the sample file and provide guidance for your concern. In case you prefer not sharing the file publicly in the forum, I have initiated a private message so you can go and share me the file there and I will try my best to assist you within my abilities.

Thank you for your patience and understanding. Hope to hear from you soon.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
santosh chaturvedi 0 Reputation points

2026-05-28T12:22:19.0566667+00:00

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)
Was this answer helpful?

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Comparing two columns vales from filtered table and original table and decide the values are present or not.

8 additional answers

Your answer