match two values in two ranges_lookup formula

[dvsdasari]

Hi,


Can any one help me out in this formula.


COL B COL C COL E COL E COL F

1 dept a/c code Apr-11 May-11 Jun-11

2 123 500123 10 10 10

3 456 500124 11 11 11

4 789 500123 12 12 12


=INDEX(B2:F5,MATCH(B3&C3,B2:B5&C2:C5,),MATCH(C2,B2:F2))


Here, we need to match A3 and b3 in col a and col b.


Thanks-Sekkhar

 

[NARAYANK991]

Hi Sekhar ,


Can you give more details on what result you want ?


What is in Column A ? Is it the numbers 1,2,3,4,.... ?


What is in cell C2 ?


What is the purpose of the MATCH(C2,B2:F2) formula ?


Narayan

 

[dvsdasari]

Hi Narayan,


Thanks for your quick reply.......


There is no date in column A, it's just row number for ref.


in cell C2 is matcing criteria of headers (here it is Apr1-11). the overall creiteria here is....


We need to match with dep and a/c code for apr-11. The result should be 10.


Thanks-Sekhar

 

[NARAYANK991]

Hi Sekhar ,


Since column A is just row numbers , let us ignore it for now.


The remaining data is in columns B , C , D , E and F as shown below.


123 500123 10 10 10

456 500124 11 11 11

789 500123 12 12 12


If the criteria is April-11 , what part is played by the dept and a/c code ?


Can you copy + paste a big enough section of your worksheet here , including all the headers , criteria cell , and give the top left cell address of the range you have posted ?


Narayan

 

[dvsdasari]

COL A COL B COL C COL E COL E COL F

1 dept a/c code Apr-11 May-11 Jun-11

2 123 500123 10 10 10

3 456 500124 11 11 11

4 789 500123 12 12 12

 

[NARAYANK991]

Hi Sekhar ,


Can you clarify how you want to lookup / match values ?


Narayan