LOOKUP FORMULA BASED ON HEADERS

%3CLINGO-SUB%20id%3D%22lingo-sub-1948036%22%20slang%3D%22en-US%22%3ELOOKUP%20FORMULA%20BASED%20ON%20HEADERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948036%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20some%20data%2C%20which%20consists%20of%20account%20numbers%20(rows)%20by%20month%20(headers).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20attempting%20to%20create%20a%20formula%20that%20will%20return%20the%20value%20of%20a%20cell%20based%20on%20the%20date%20I%20enter.%20For%20example%2C%20if%20I%20enter%20Nov%2020%2C%20I%20want%20the%20formula%20to%20search%20for%20that%20date%20in%20the%20headers%20and%20return%20the%20values%20of%20the%20account%20numbers%20in%20that%20column%2C%20for%20Nov%2020.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20tried%20using%20INDEX%20%26amp%3B%20MATCH%2C%20VLOOKUP%20with%20an%20MATCH%20function%20and%20SUMIFS%2C%20but%20none%20of%20these%20have%20worked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20anyone%20suggest%20any%20other%20formulas%20which%20may%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1948036%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948503%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20FORMULA%20BASED%20ON%20HEADERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948503%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847248%22%20target%3D%22_blank%22%3E%40Seaneboy2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20go%20a%20long%20way%20toward%20helping%20us%20help%20you%20by%20posting%20a%20copy%20(or%20a%20representative%20sample)%20of%20your%20spreadsheet.%20Your%20verbal%20description%20is%20confusing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20%3CSTRONG%3Eactual%20sample%20of%20the%20spreadsheet%3C%2FSTRONG%3E%20itself%20is%20better%20than%20an%20image%2C%20and%20would%20also%20enable%20somebody%20here%20to%20write%20a%20formula%20that%20works%20and%20post%20it%20for%20you%20to%20use.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948588%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20FORMULA%20BASED%20ON%20HEADERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948588%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20included%20an%20example%20of%20the%20spreadsheet%2C%20as%20requested.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20populate%20the%20cells%20of%20column%20N%2C%20with%20the%20values%20in%20column%20I.%20I%20want%20to%20use%20the%20date%20in%20cell%20P1%2C%20as%20the%20search%20criteria.%20So%20any%20the%20values%20of%20any%20cells%20contained%20within%20a%20column%2C%20where%20the%20dates%20agree%20are%20shown%20in%20column%20N.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20need%20any%20further%20information%2C%20please%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948663%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20FORMULA%20BASED%20ON%20HEADERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948663%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847248%22%20target%3D%22_blank%22%3E%40Seaneboy2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnter%20this%20formula%20in%20cell%20N2%2C%20and%20copy%20it%20down.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(B2%3AM2%2C%2CMATCH(%24P%241%2C%24B%241%3A%24M%241%2C0))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20explain%20the%20formula%3A%20the%20MATCH%20function%20finds%20the%20column%20number%20in%20the%20array%20of%20headings%20that%20matches%20your%20date%20in%20P1%26nbsp%3B%26nbsp%3B%20The%20dollar%20signs%20keep%20the%20references%20constant%20even%20as%20the%20total%20formula%20is%20copied%20to%20succeeding%20rows.%3C%2FP%3E%3CP%3EThe%20INDEX%20function%20uses%20that%20number%20to%20find%20the%20corresponding%20column%20in%20each%20row%20as%20the%20formula%20is%20copied%20down%20to%20succeeding%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20this%20is%20in%20fact%20doing%20what%20you%20want.%20I'm%20curious%20what%20the%20actual%20application%20is%20here.%20It%20seems%20an%20unusual%20way%20to%20organize%20the%20information...so%20I'm%20wondering%20whether%20the%20whole%20spreadsheet%20or%20workbook%20could%20use%20a%20makeover.%20But%20of%20course%2C%20it's%20not%20my%20business%20so%20feel%20free%20to%20ignore%20the%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948870%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUP%20FORMULA%20BASED%20ON%20HEADERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBrilliant%2C%20this%20is%20now%20working!%20When%20I%20was%20using%20the%20INDEX%20%26amp%3B%20FUNCTION%20before%2C%20I%20was%20selecting%20the%20entire%20array%2C%20so%20that%20must%20of%20been%20why%20it%20didn't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20application%20is%20for%20use%20within%20an%20a%20financial%20statement%20report.%20The%20example%20I%20sent%20shows%20how%20the%20TB%20data%20would%20be%20formatted%20within%20the%20report.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20set%20it%20up%2C%20so%20when%20the%20user%20enters%20the%20relevant%20period%20end%20date%2C%20the%20formulas%20will%20automatically%20pull%20in%20the%20correct%20data%20for%20the%20relevant%20period.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20in%20the%20process%20of%20giving%20all%20worksheets%20and%20workbooks%20an%20efficiency%20drive%20makeover%2C%20so%20any%20input%20would%20be%20very%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

 

I have some data, which consists of account numbers (rows) by month (headers).

 

I'm attempting to create a formula that will return the value of a cell based on the date I enter. For example, if I enter Nov 20, I want the formula to search for that date in the headers and return the values of the account numbers in that column, for Nov 20.

 

So far I have tried using INDEX & MATCH, VLOOKUP with an MATCH function and SUMIFS, but none of these have worked.

 

Could anyone suggest any other formulas which may work?

 

Thanks

 

9 Replies

@Seaneboy2020 

 

You could go a long way toward helping us help you by posting a copy (or a representative sample) of your spreadsheet. Your verbal description is confusing.

 

An actual sample of the spreadsheet itself is better than an image, and would also enable somebody here to write a formula that works and post it for you to use.

@mathetes 

 

Thank you for your message.

 

I have included an example of the spreadsheet, as requested.

 

I am trying to populate the cells of column N, with the values in column I. I want to use the date in cell P1, as the search criteria. So any the values of any cells contained within a column, where the dates agree are shown in column N.

 

If you need any further information, please let me know.

 

Thank you.

@Seaneboy2020 

 

Enter this formula in cell N2, and copy it down.

=INDEX(B2:M2,,MATCH($P$1,$B$1:$M$1,0))

 

To explain the formula: the MATCH function finds the column number in the array of headings that matches your date in P1   The dollar signs keep the references constant even as the total formula is copied to succeeding rows.

The INDEX function uses that number to find the corresponding column in each row as the formula is copied down to succeeding rows.

 

Let me know if this is in fact doing what you want. I'm curious what the actual application is here. It seems an unusual way to organize the information...so I'm wondering whether the whole spreadsheet or workbook could use a makeover. But of course, it's not my business so feel free to ignore the question.

@mathetes 

 

 

Brilliant, this is now working! When I was using the INDEX & FUNCTION before, I was selecting the entire array, so that must of been why it didn't work.

 

The application is for use within an a financial statement report. The example I sent shows how the TB data would be formatted within the report.

 

I'm trying to set it up, so when the user enters the relevant period end date, the formulas will automatically pull in the correct data for the relevant period.

 

I'm in the process of giving all worksheets and workbooks an efficiency drive makeover, so any input would be very welcome.

 

Thanks again for your help.

 

 

@Seaneboy2020 

 

I'm in the process of giving all worksheets and workbooks an efficiency drive makeover, so any input would be very welcome.

 

Well, without knowing a lot more it would be entirely presumptuous to offer suggestions. Who the user(s) is (are) going to be for example.

 

In general, though, the layout you had seemed less than functional for the long term. To keep adding monthly data as a new column works for a while, but there is a limit to columns; and long before that, just navigating the screen becomes unwieldly.

 

Most databases are better thought of as rows and rows of data with the different fields of distinct types of data reflected in the columns. A set of rows, with both account number and month as separate fields in each row (along with other monthly data) would still be searchable and amenable to summarization by account, by month, by other characteristics ...

 

 

@mathetes 

Bit more modern variant

=XLOOKUP($P$1, $B$1:$M$1, B2:INDEX(M:M,XMATCH(,A:A)-1))

@Seaneboy2020 

I suggest this formula in O2 of the attached version of your file: 

=INDEX(ColumnsLeft,
MATCH(Q$1,HeadersLeft,0))

 

ColumnsLeft refers to: 

=Schedule!$B2:N2

 

HeadersLeft refers to: 

=Schedule!$B$1:N$1

 

Kindly notice these: 

1. I modified the formula starting in C1 to this: 

=EOMONTH(B1,1)

 

2. I inserted a column for April 2021, with simulated values thereunder. 

 

3. You can also insert a column for May 2021 to test the dynamic attribute of ColumnsLeft and HeadersLeft.

Thank you very much for this example.

This formula will certainly help a lot with making my spreadsheets more dynamic.
You're very much welcome!