Multi Part Lookup

[MikeD1224]

Store # Jan Feb Mar

1 471,374 488,292
3 - -
4 488,295 470,050
5 122,927 113,160

I have the data above and need to do a 2 part lookup. I need to do a lookup
on store 1 and then do a lookup on Jan to return the value (471,374). I need
to do this for each store, for each month of the year. I am trying to only
write 1 formula that will first match the correct store and then do an
hlookup on the appropriate month.

I need help. Thanks.

 

[akphidelt]

I've ran in to this problem couple times. It all depends on how your lookup
is set up. What are you using?? Cells, comboboxs, inputbox? But just for
starters I usually use the offset formula but that requires additional set
up. What you can do is a vlookup

=Vlookup(Store#,YourRange,Month(YourDate)+1,FALSE)

The Month() formula gives you a serial number of the month. So the vlookup
would take the number of the month and use that as it's horizontal lookup. So
it all depends on how you are looking up this value.

 

[Dave Peterson]

You could use =index(match())

Debra Dalgleish has lots of notes
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[Pete_UK]

Suppose you use cell N1 for the store number and N2 for the month that
you are interested in. This formula will get you the appropriate
value:

=INDEX(B2:M5,N1,N2)

assuming your data occupies rows 2 to 5 and columns B to M with your
labels in column A and row 1.

Hope this helps.

Pete