LOOKUP

[niting]

Hey Forum,


I have a sheet 1 with 3 columns, COl A with A/c codes, Col B with the month Names, Col C with the values. In sheet2 I have col A with A/c codes and row A1 with Month names and I want to poplulate the Col B with values corrosponding to the month and A/c codes in Col C in the 1st Sheet.


One option I have is to use Vlookup, but then I would have to manually adjust the range for every month because the same A/c codes are there in different months. I know I can use Index Match, but when i Tried I could use only 1 condition, either month or the A/c Code.


Any suggestions for my problem.


thanks in advance


Nitin

 

[Luke M]

I believe you want something like this in cell B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1!$B$2:$B$100=B$1),(Sheet1!$C$2:$C$100))


You can then copy it down and/or across as needed.

 

[niting]

Luke,


Thanks a ton!!!


I know there are many ways of solving a same problem, and excuse my over zealousness, but could we have also used Index and Match or any other Lookup tools to solve the same problem???


Thanks again for the prompt reply


Nitin

 

[Luke M]

Possibly. Here's an array formula using INDEX:


=INDEX(Sheet1$C:$C,IF((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1),ROW($B$2:$B$100)))


Note that this assumes there is only one line where Month and A/c code match your criteria.


Or, if you have 2007 or later, a SUMIFS function would work.