CODEWITHSUNDEEP
excel
Chris Toh
Chris Toh

Reputation: 19

lookup value in vlookup function involving dates

Currently I'm trying to pull information from a data table(monthdata) from another worksheet.

Formula look like this =vlookup(c1,monthdata,5,false)

c1 is the column of dates data (e.g 3-Mar)

monthdata consists of the 12 different months named in Jan, Feb, Mar...etc along with 5 columns of data I'm trying to pull.

Problem here is i have no idea how to make c1 (lookup_value) to be able to recognize that I just wanted data involving in the month March rather then a specific date in march.

Is there a way i can have the lookup value to solely recognize March without changing c1 data format?

Upvotes: 0

Views: 442

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7884

Use this formula:

=VLOOKUP(TEXT(C1,"[$-409]mmm"),monthdata,5,FALSE)

Data format in C1 has nothing to do with this - the dates are stored (and passed to formulas) as numbers like 42067. Formatting only determines how they are displayed in cell.

TEXT formula extracts 3-letter English month code from the date stored in C1. mmm here means 3-letter month, [$-409] is for U.S. English locale.

Upvotes: 0

Related Questions