Matching Data in excel

[hayden_pinto]

Hi


I have a report that currently has 2 critirios to be found and matched,

and i use the following excel formula

=INDEX($F$1:$F$10000,MATCH(A1,$D$1:$D$10000,0)+MATCH(B1,OFFSET(INDEX($E$1:$E$10000,MATCH(A1,$D$1:$D$10000,0)),0,0,COUNTIF($D$1:$D$10000,A1)),0)-1)


Now i have to match 3 critirios


how can this be changed to include 3 critirios

 

[SirJB7]

Hi, hayden_pinto!

Which would be the third criteria? Columns, test condition...

Consider uploading a sample file.

Regards!

 

[Faseeh]

Hi hayden_pinto,


If your criteria is present in A1, B1 & C1 and you are looking for them in Columns D, E & F you can use following formula:

=MATCH(A1&B1&C1,D1:D1000&E1:E1000&F1:F1000,0)

..and press ctrl+shift+enter. (enter as array formula) Once you got result of match use Index() or Offset() to reach any Row you want..


Regards,

Faseeh

 

[hayden_pinto]

I have tryed (ctrl+shift+enter) but it takes very long when dealing with large amounts of data , i was hoping someone would have a formula to search and match 3 criterios without (ctrl+shift+enter) cuz i have tryed this and it works but takes 20 mins to calculate


this is the formula that i came up with but its just to slow

{=Index(G:G,(Match(A1 & B1 & C1, D:D & E:E & F:F,0)))}


Data in A1,B1,C1 will be Matched in D,E,F and if found then the result will be the same row in coloum G


this is what i need without the (ctrl+shift+enter)

 

[hayden_pinto]

I have tryed (ctrl+shift+enter) but it takes very long when dealing with large amounts of data , i was hoping someone would have a formula to search and match 3 criterios without (ctrl+shift+enter) cuz i have tryed this and it works but takes 20 mins to calculate


this is the formula that i came up with but its just to slow

{=Index(G:G,(Match(A1 & B1 & C1, D:D & E:E & F:F,0)))}


Data in A1,B1,C1 will be Matched in D,E,F and if found then the result will be the same row in coloum G


this is what i need without the (ctrl+shift+enter)

 

[NARAYANK991]

Hi Hayden ,


I think part of the problem lies in specification ; first one specification is posted , and once the answer to that is received , a second specification is posted , so that once more the same thought process has to go in arriving at a solution to the second question.


I think you can get a solution to your problem if you use the method which has been used to arrive at the solution to your first question.


Let us assume that the three keys are Key1 , Key2 and Key3 ; if your data is in three columns , say Col1 , Col2 , Col3 ; if we can assume that the three columns of data are sorted in the order Col1 , then Col2 and lastly Col3 , then the solution is as follows :


1. Look up the Key1 in Col1 , if not found there is absolutely no possibility of a match.


2. If a match is found , then starting from that point on in Col2 , for as many entries as are available in Col1 matching Key1 , see if there is a match for Key2 within these entries.


3. If a match is found , then repeat step 2 , using Key3 in Col3 , for as many entries which have Col1 matching Key1 and Col2 matching Key2.


I think Chandoo's optimization week posts should be useful to get a reasonably fast solution for this.


Narayan

 

[Montrey]

I laughed very hard at Hayden's Avatar! LOL!