Forum Discussion
LOOKUP FORMULA BASED ON HEADERS
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 ex...
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.
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!