SOLVED

# Formula and function

Copper Contributor

# Formula and function

I am looknig for a formula where only the date change can pick the correspondant figure from the table for a particular material at a particular cell. example below;

 PRODUCT NAME 5/14/2023 PRODUCT NAMES Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 MATERIAL A ? MATERIAL A 101 102 103 104 105 106 107 108 109 110 111 112 MATERIAL B ? MATERIAL B 201 202 203 204 205 206 207 208 209 210 211 212 MATERIAL C ? MATERIAL C 301 302 303 304 305 306 307 308 309 310 311 312 EXCHANGE RATE ? EXCHANGE RATE 401 402 403 404 405 406 407 408 409 410 411 412
4 Replies

# Re: Formula and function

@urb1985
Row4: B2 has the day number (14) subtracted to find the same figure as the month.

Months may be formatted as in your example to not show the day (-01 with my local settings).

``````=LET(months,     \$E\$1:\$O\$1,
figures,    E2:O2,
lookupDate, \$B\$1,
lookupMonth,     lookupDate-DAY(lookupDate)+1,
output,          XLOOKUP(lookupMonth,months,figures),
output
)``````

If older Excel, it can be done using hlookup.

# Re: Formula and function

Thank you Bosinander for your help however unfortunately the provided formulas not working in my office, i am using Microsoft office 2013, can you drive formula for that version? many thanks.
best response confirmed by urb1985 (Copper Contributor)
Solution

# Re: Formula and function

In B2:

=INDEX(E2:O2,MATCH(\$B\$1-DAY(\$B\$1)+1,\$E\$1:\$O\$1,0))

Fill down.

# Re: Formula and function

This is working perfectly!

Really Thankful.
1 best response

Accepted Solutions
best response confirmed by urb1985 (Copper Contributor)
Solution

# Re: Formula and function

In B2:

=INDEX(E2:O2,MATCH(\$B\$1-DAY(\$B\$1)+1,\$E\$1:\$O\$1,0))

Fill down.