Index Match function when mutiple criteria meets and extract the date

[FUN2BALA]

Hi,

Kindly guide me, attaching the excel file.

Where there is a name and vehicle no. and no days .
here i have to extract the days where the name and vehicle no is matched. i tried using index match function but unable to sort it out.

required answer will be like this,
CP2710
aanand 1, 2, 3, 4

ALL THIS DAYS has been present wherever condition met it has to extract the days
i have highlighted the row and colum in req sheet with yellow color.

[dflak]

This formula works as an array formula in cell S2. Copy / Paste to other cells.

This formula only works if the names are in the same order on both sheets.
=IFERROR(LEFT(SUBSTITUTE(CONCAT(IF(Data!$B3:$AE3=Req!S$1,Data!$B$2:$AE$2&",",FALSE)),FALSE,""),LEN(SUBSTITUTE(CONCAT(IF(Data!$B3:$AE3=Req!S$1,Data!$B$2:$AE$2&",",FALSE)),FALSE,""))-1),"")

I only copied the formulas to the orange-shaded cells. If you want an explanation as to why this formula works, just ask.

[FUN2BALA]

Thanks dflak,

for the quick response and it seems to be new formula for me
"concat" i searched this in net i learned something new thanks for that.

but i m using ms office 2016 not office365 where this function is not available in my version. i tired to update office also but no use. is there any alternate.

[dflak]

I am using Excel 2016 here in the office and Excel 365 at home. It is available in both places. According to this: https://www.techonthenet.com/excel/formulas/concat.php, it should be available in Excel 2016.

[FUN2BALA]

regret to inform u i m unable to use that formula in my excel version i searched in internet also whether any other way to use concat function. i m unable to dod so.

[FUN2BALA]

i tried using concatenate but not getting as required, can u explain on it, will be a good use.

[sandy666]

CONCAT isn't exist in 2016 Desktop

This feature is only available if you have an Office 365 subscription.

[sandy666]

Is that what you want?

Done with PowerQuery aka Get&Transform

btw. update your profile about your real excel version

General Note: Read me Try to avoid joining to my Black List by doing this below If that takes care of your original question, & to say Thanks and for better Motivation, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. If you did it already - ignore it. Thank you.

[FUN2BALA]

yes i want this. but can u tell me how to do this i installed power query also and tried something but unable to do.

[sandy666]

You said

i m using ms office 2016

so update your profile first,
and PowerQuery exist in Excel 2016 by default so you don't need to install it. (but not in H&S version )

ad rem:
you need to clean your source data from unnecessary things like OFF, RININGIN, A, SWITCH OFF etc.
you need to remove duplicates and change all names to the same format not mixed (small and caps) - all you can do via PQ Editor, as you can see in example
if you do that the rest of steps are here: (Ribbon - Data tab, Show Queries, dbl click on table from the right side)

steps.jpg

[FUN2BALA]

Thanks sandy666,

Sorry for late reply.

i tried and did as per instructions.

its worked
but i want to know in added custom1 we used formula. can u explain it. it will be great help and appreciate it.

[sandy666]

I think you need re-read whole post#8 (General Note)

ad rem:
step8: you've Grouped Rows - column with Table (Count), click once on any "cell" with table in the column you'll see what table contain. You need only Day column from there so if you edit
step9 you'll see how to extract this column to the list. and again click once on any list ("cell") in this column Day you'll see what List contain. You need extract these values (Days) to the row separated by comma so step10: Extract Values
and so on...

List.Distinct(Table.Column([Count],"Day")) - create Distinct List from table (mentioned above) from extracted column "Day"

anything else ?

[FUN2BALA]

thank u v much. i appreciate for taking time and explaining the things.

can u tell me if i need to learn this atleast basic, which site or link to be referred.

[sandy666]

basic of what? PowerQuery?

• Getting Started with Get & Transform in Excel 2016
• Microsoft Power Query for Excel Help
• Google.com

[FUN2BALA]

Thank u Mr. sandy666

[sandy666]

You are welcome

Have a nice day