Extract row for specific cells with 2 Criterias

Hello Friends

In Sheet 1 for Column B filter only for `ABC dmkijtfsh' then in Column BA Text filter `NT'

Refer the required result in Sheet 2 Light yellow cells, Sheet 3 shows the after filtered view for better understanding.

thanks in advance

Sekar

1st you need to use real headings for your data, and those headings need to be repeated in your extract table
for the sake of simplicity, I used 1 (col B) to 54 (BC), then repeated those numbers in your extract table (4, 10, 26 etc)

I used this ARRAY formula...
=IFERROR(INDEX(Sheet1!$B:$BC,SMALL(IF((Sheet1!$B$4:$B$87="ABC dmkijtfsh")*(Sheet1!$BA$4:$BA$87="NT"),ROW(Sheet1!$B$4:$B$87)),ROWS($A$1:A1)),MATCH(B$54,Sheet1!$B$2:$BC$2,0)),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Copy that down as needed, then copy across as needed.

I have NO idea where you got the values from in col E?

These array formula shows blank even after Ctrl+Shift+Enter

Did you change the headings like I told you to?

This is what I got....

	B	C	D	E
53	4	10	26
54	4	10	26	45
55	a	72	Y123_XYZ	148
56	a	79	Z234_ABC	901
57	a	91	B123_ABC	773
58	Column E	Column K	Column AA	Column AT
59	My Extract
60	a	72	Y123_XYZ	2
61	a	79	Z234_ABC	2
62	a	91	B123_ABC	2

Can you please upload a file, i tried still i am getting blank cells.

See sheet 4. In A2, copied across and down:

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$A$4:$A$100)/((Sheet1!$B$4:$B$100="ABC dmkijtfsh")*(Sheet1!$BA$4:$BA$100="NT")),ROWS(A$2:A2))),"")

Here is your file.

Why not go with Pivot Table.
One logic not know about sheet2 column "E" how comes 148,901, 773
Refer attach file.

avh I couldnt figure that out either. Seeing as they were manually entered, probably just "garbage" values?

FDibbins : Yes i also that way think. Let's waiting for their reply. Have a good day.

Hello Glenn, Ford and avk

Thanks for all, prefer to go for Non Array glenn's formula, not preferred for pivot table.

Also i have not seen glenn's post so i editied Ford's formula to suit for my application since i cannot allow the manually entered numbers in Sheet 1 Say input sheet.

Please refer the attached file Extract row for specific cells with Creterias-2.xlsx Sheet2 Cells B60:E62,

Once again thank to all.

Sekars

Happy to help and thanks for the feedback