Multiple Search Criteria/ Index Match

C

Christine Wilso

I am using the following formula to retrieve data that matches tw
specific criteria.
=INDEX(C2:C80,MATCH(1,(A2:A80="Liverpool")*(B2:B80="January"),0))
using ctrl,shift, enter to give curly brackets to make it work.

This formula is then repeated in the cell directly below, but searche
for "February", below that "March" and so on.
However when I copy my formula, I have to change the month for eac
cell and therefore have to put in the curly brackets again.
Is there anyway around this
 
L

litos_aldovea

Have a list with the month somewhere else, lets say A1:A12 are January
Feb,.......

Then if you refer the first "January" to A1, when you copy your formul
afterwards it should work.

I hope this helps,

Carlos Lopez
 
D

Dave Peterson

Another option is to adjust your formula based on the row it's on:

=INDEX(C2:C80,MATCH(1,(A2:A80="Liverpool")
*(B2:B80=TEXT(DATE(2004,ROW(),1),"mmmm")),0))

If it's the formula is in row 2, then:
text(date(2004,2,1),"mmmm") will return February.

But you may want to look at pivottables. They might make all this moot.
They're pretty easy to use after you invest a little time learning.

If you want to learn more about pivottables, here are a few links.

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
Top