how to find data with match

[Rahul Mishra]

Sir i Asked one question again plz help me

 

[JAMIR]

Hello Rahul

Please be patience. All member and Expert are busy with dealing someone.

Here is nothing is urgent.....

Zameer

 

[Ratan Bhushan]

The data you have provided here has merge cells, therefore, match function not working out...
If we use hlookup or vlookup, we need to give manual values to search
Please try to put the data in the one column and try to stretch the cell according to desire.
Please revert

 

[Ratan Bhushan]

Here is your answer
I have attached the file.. I hope this resolves your query

 

[Somendra Misra]

Hi Rahul,

See your file, formula in orange cells.

Regards,

 

[Ratan Bhushan]

Hi Rahul,

See your file, formula in orange cells.

Regards,

Sir can you please describe the method you have used.... I am unable to get it..

 

[Somendra Misra]

@Ratan Bhushan

I had used 2nd form of INDEX function.

INDEX(reference, row_num, column_num, area_num)

Regards,

 

[Ratan Bhushan]

@Ratan Bhushan

I had used 2nd form of INDEX function.

INDEX(reference, row_num, column_num, area_num)
Regards,

Thank u sir, I shall try to work on it...

 

[Rahul Mishra]

sir thank you so much but it is so long i am not able to understand this part of formula can you discribe this MATCH(INDEX(LOOKUP(COLUMN($E$21:E$21),COLUMN($E$21:E$21)/($E$21:E$21<>""),$E$21:E$21),,COLUMNS($E23:E23))&E22,LOOKUP(COLUMN($C$3:$T$3),COLUMN($C$3:$T$3)/($C$3:$T$3<>""),$C$3:$T$3)&$C$4:$T$4,0),MONTH($E$20&1))

 

[Somendra Misra]

@Rahul Mishra

MATCH(INDEX(LOOKUP(COLUMN($E$21:E$21),COLUMN($E$21:E$21)/($E$21:E$21<>""),$E$21:E$21),,COLUMNS($E23:E23))&E22,LOOKUP(COLUMN($C$3:$T$3),COLUMN($C$3:$T$3)/($C$3:$T$3<>""),$C$3:$T$3)&$C$4:$T$4,0)

This part of the formula gives the column number to INDEX function. Now the problem with your data set there are merged cells both in data table as well as extraction table.

So if any thing like this:

Service                                        Parts                                    Service                                        Parts

Should be like this to create a simple formula:

Service      Service    Parts    Parts

But when you merge cells only data of first cells remains and rest all cells becomes 0.

So we need an array like Service Service Parts Parts.

To get that array in a formula: I had used below parts:
LOOKUP(COLUMN($E$21:E$21),COLUMN($E$21:E$21)/($E$21:E$21<>""),$E$21:E$21)

Basically this function will fill the blanks of merged cells with the data which is there at the starting of merge cells.

Then it has been concatenated with Tar or Ach to create string likes "ServiceTar" or "ServiceAch" or "PartsTar" or like that. Now this is repeating in 3 areas, 1 for Jan, 2nd for Feb and 3rd for March.

So match function will finally give the column number.

Than the forurth argument is area number of the outer most INDEX function.
Which I got from MONTH function to give 1,2 or 3 as there are only 3 areas.

I hope this will clear your doubt, if not than write back.

Regards,