Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Part Lookup
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Part Lookup
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. "MikeD1224" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Part Lookup
You could use =index(match())
Debra Dalgleish has lots of notes http://www.contextures.com/xlFunctions03.html (for =index(match())) MikeD1224 wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multi Part Lookup
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 On Apr 18, 6:16*pm, MikeD1224 wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi column lookup | Excel Discussion (Misc queries) | |||
Lookup multi criteria | Excel Worksheet Functions | |||
Find last name in multi-part name? | Excel Worksheet Functions | |||
lookup multi values | Excel Worksheet Functions | |||
Multi Criteria lookup | Excel Discussion (Misc queries) |