Date Lookup and Value Return

[aceyus_michael]

I am running into a major issue doing an index and match with a date value. Here is the formula I am using:
=INDEX($B$10:$P$11,MATCH(B13,$B$11:$P$11,0)-1)

I need to find a date in a row and then return the value in the cell located above the date matched. I have included a sample of the table. PLEASE help me out as I need this for my forecasts and I can not get this to return my value which is screwing up my entire forecast!

Here is a link to the portion of the data I am having issues with.

iCloud Drive - Apple iCloud

Store, organize and collaborate on files and folders with iCloud Drive. Easily upload, delete or recover files. Changes will sync across your devices with iCloud.

www.icloud.com

Sorry, our o365 is locked down from outside access so could only share this via a link to a shared folder on my personal iCloud.

 

[AhoyNC]

Your dates don't match. In cell I13 the value is 8/1/21, but your formula in C11 has a date of 8/28/21 when you added the 300 from R2.
maybe change the formula in C11 to:
IF(C10>=1,EOMONTH(C4+$R$2,-1)+1,"No Sales").
then change your INEDX formula to:
=INDEX($B$10:$P$10,MATCH(I13,$B$11:$P$11,0))

 

[amritabansal]

Vlookup Formula​

Summary

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.

Purpose

Lookup a value in a table by matching on the first column

Return Value

The matched value from a table.

Syntax

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

Arguments

look_value - The value to look for in the first column of a table.

table_array - The table from which to retrieve a value.

col_index - The column in the table from which to retrieve a value.

range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

Index Formula

The INDEX function in Excel is fantastically flexible and powerful, and you'll find it in a huge number of Excel formulas, especially advanced formulas. But what does INDEX actually do? In a nutshell, INDEX retrieves values at a given location in a list or table.

=INDEX(array, row_num, column_num)

Match Formula

The MATCH function is designed for one purpose: find the numeric position of an item in a list.

=MATCH(lookup_value, lookup_array, match_type)

Index Match together

INDEX and MATCH functions gives you better control over how the row index number and column index number changes. This is often referred to as a dynamic formula. You will learn how this dynamic duo can help prevent errors and improve your INDEX formulas.

=INDEX(array, MATCH(lookup_value, lookup_array, match_type), MATCH(lookup_value, lookup_array, match_type))

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option that may suit if you want to leave the dates as they are, provided your Excel 365 has the FILTER function.
In B14 and copied across

=INDEX(FILTER($B$10:$P$10,TEXT($B$11:$P$11,"myy")=TEXT(B13,"myy"),""),1)