#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 461
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi column lookup Cain Excel Discussion (Misc queries) 0 April 17th 08 10:01 AM
Lookup multi criteria oscarcounts Excel Worksheet Functions 7 March 11th 08 07:22 PM
Find last name in multi-part name? Eric Excel Worksheet Functions 4 November 1st 07 05:51 PM
lookup multi values robtlewis957 Excel Worksheet Functions 2 August 25th 06 03:26 AM
Multi Criteria lookup epotter Excel Discussion (Misc queries) 3 July 6th 06 12:32 AM


All times are GMT +1. The time now is 07:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"