Embedded lookup
in [Functions]
|
Prev: Question About Importing Columns From A Datasheet to A Worksheet
Next: Count number of rows with specific text
From: HotaG on 24 May 2010 14:20 Can a lookup be embedded in another lookup? A file has separate sheets for each month with identical formats. A file has 13 sheets, one for each month and a YTD sheet. Each sheet has P&L data for multiple companies and the YTD sheet has a data entry field to identify the current month. Based on the value in that field, the lookup function should, first, go to the correct month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for April; All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will lookup A1, go to the April sheet, then perform the lookup function.
From: Luke M on 24 May 2010 14:32 Take a look at using the INDIRECT function, perhaps something like: =VLOOKUP(A3,INDIRECT("'"&A1&"'!A:B),2,FALSE) -- Best Regards, Luke M "HotaG" <HotaG(a)discussions.microsoft.com> wrote in message news:078B9944-0900-492B-9F98-6D9183077ECF(a)microsoft.com... > Can a lookup be embedded in another lookup? A file has separate sheets for > each month with identical formats. A file has 13 sheets, one for each > month > and a YTD sheet. Each sheet has P&L data for multiple companies and the > YTD > sheet has a data entry field to identify the current month. Based on the > value in that field, the lookup function should, first, go to the correct > month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for > April; > All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will > lookup > A1, go to the April sheet, then perform the lookup function.
From: Steve Dunn on 25 May 2010 05:45 Modified to allow numeric value for month in A1: =VLOOKUP(A3,INDIRECT("'"&LOOKUP(A1,{1,2,3,4,5,6,7,8,9,10,11,12}, {"January","February","March","April","May","June", "July","August","September","October","November","December"})& "'!A:B),2,FALSE) "Luke M" <lukemoraga(a)nospam.com> wrote in message news:%23ph1A%232%23KHA.5476(a)TK2MSFTNGP06.phx.gbl... > Take a look at using the INDIRECT function, perhaps something like: > > =VLOOKUP(A3,INDIRECT("'"&A1&"'!A:B),2,FALSE) > > -- > Best Regards, > > Luke M > "HotaG" <HotaG(a)discussions.microsoft.com> wrote in message > news:078B9944-0900-492B-9F98-6D9183077ECF(a)microsoft.com... >> Can a lookup be embedded in another lookup? A file has separate sheets >> for >> each month with identical formats. A file has 13 sheets, one for each >> month >> and a YTD sheet. Each sheet has P&L data for multiple companies and the >> YTD >> sheet has a data entry field to identify the current month. Based on the >> value in that field, the lookup function should, first, go to the correct >> month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for >> April; >> All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will >> lookup >> A1, go to the April sheet, then perform the lookup function. > >
From: Steve Dunn on 25 May 2010 05:50
Erm, got a bit carried away there, try this instead: =VLOOKUP(A3,INDIRECT("'"&TEXT(DATE(2010,A1,1),"mmmm")& "'!A:B),2,FALSE) "Steve Dunn" <stunn(a)sky.com> wrote in message news:Olqot6%23%23KHA.4652(a)TK2MSFTNGP06.phx.gbl... > Modified to allow numeric value for month in A1: > > =VLOOKUP(A3,INDIRECT("'"&LOOKUP(A1,{1,2,3,4,5,6,7,8,9,10,11,12}, > {"January","February","March","April","May","June", > "July","August","September","October","November","December"})& > "'!A:B),2,FALSE) > > > > "Luke M" <lukemoraga(a)nospam.com> wrote in message > news:%23ph1A%232%23KHA.5476(a)TK2MSFTNGP06.phx.gbl... >> Take a look at using the INDIRECT function, perhaps something like: >> >> =VLOOKUP(A3,INDIRECT("'"&A1&"'!A:B),2,FALSE) >> >> -- >> Best Regards, >> >> Luke M >> "HotaG" <HotaG(a)discussions.microsoft.com> wrote in message >> news:078B9944-0900-492B-9F98-6D9183077ECF(a)microsoft.com... >>> Can a lookup be embedded in another lookup? A file has separate sheets >>> for >>> each month with identical formats. A file has 13 sheets, one for each >>> month >>> and a YTD sheet. Each sheet has P&L data for multiple companies and the >>> YTD >>> sheet has a data entry field to identify the current month. Based on >>> the >>> value in that field, the lookup function should, first, go to the >>> correct >>> month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for >>> April; >>> All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will >>> lookup >>> A1, go to the April sheet, then perform the lookup function. >> >> > |