Search for two or more matching results in rows..

Hi,

I need to use Find function to Find out two or more Matching Results comparing the data on a sheet to find the exact column where the data is available

Eg.

I have a 3 rows with data

ROW J "ITEM"
ROW K "MODEL"
ROW L "MAKE"

now i need to search for the ALL 3 ROWS TO MATCH THE DATA I NEED TO FIND

12.PNG


*************result should be SIDE MIRROR +PLEASURE N/M + UNBRANDED
*PLEASE CHECK ATTACHMENT

You are getting ROWS mixed up with COLUMNS, which is confusing your description.

Pete

My Bad,,

I have attached the Document for clear demonstration.

For example i have few items in my inventory and they are categorized item,colour and made
NOW I NEED TO SEARCH FOR COLUMN THAT HAVE THE EXACT MATCH FOR MY QUERY .
ITEM :APPLE / COLOR :GREEN / MADE : CHINA By using a Find function or any other available methods.

SAMPLE.JPG

*

First of all, to make this more flexible, you need to be able to record the items that you are searching for, so you could use E2, F2 and G2 for ITEM, COLOUR and MADE (which you could copy into E1:G1), so put Apple, Green and China in E2:G2, then you can use this array* formula in cell I2:

=IFERROR("Found in Row "&INDEX(ROW(2:10),MATCH(1,(A2:A10=E2)*(B2:B10=F2)*(C2:C10=G2),0),),"Not found")

*Note that if you are using XL2019 or earlier, an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Note also, that you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

This assumes that there would only be one match to the criteria, as it will return the first row where the match occurs.

Hope this helps.

Pete

HI Pete,
It worked and Thank you for your time,

but What if a have more than 1 match to the criteria to find ?
and what if i need to search the entire book for the results ?

Thank you

Attach another workbook with examples of more than one match - you could use a helper column to identify these.

I'm not sure what you mean by "search the entire book". Are you going to have more than 3 columns, or use rows down to the bottom of the sheet (1 million +).

Pete

Hi,
I have attached the sample workbook .
i am going to use rows down to the bottom of the sheet and the date is added time to time ...........

thankyou

There is no new attachment, and I don't know what this means:

... the date is added time to time ...

Help us to help you.

Pete

attachment ,,,,,

i use excel 2010..

Originally Posted by fariskhan007

Hi,
I have attached the sample workbook .
i am going to use rows down to the bottom of the sheet and the date is added time to time ...........

thankyou

sorry again!
i Mean the spreadsheet is not completed and the data is kept added time to time when new stock arrive.....

Here's another approach - put this formula in cell F2:

=IF(COUNTIFS(C2,$G$2,D2,$H$2,E2,$I$2),MAX(F$1:F1)+1,"")

Copy this down to the bottom of your data (or beyond, to accommodate new data being added). You can do this quickly by double-clicking the fill handle (in the bottom right corner of the cursor, with F2 selected).

Then you can use this formula in K2:

=IFERROR("Found in Row "&INDEX(ROW($F:$F),MATCH(ROWS($1:1),F:F,0)),"Not found")

Copy this down for as many duplicates as you think there may be (e.g. for 10 rows). You could return other data instead of my original message - e.g. this formula in K2:

=IFERROR("Found in Row "&INDEX(ROW($F:$F),MATCH(ROWS($1:2),F:F,0))&" on "&TEXT(INDEX(B:B,MATCH(ROWS($1:2),F:F,0)),"dd/mm/yyyy"),"Not found")

would return the date from column B as well as the row number where the duplicate occurs.

Hope this helps.

Pete