Populate Cells Based on Matched Criteria and Enter Value From Another Cell

Topic

New Reply

If I have my “wording” wrong in my title and/or questions, I apology in advance. Although, I have a basic knowledge of Excel functions like sum, count, max and min, etc.; I have a very limited knowledge of advanced functions or formulas.

I have a workbook (see attached) with a function in C2 that equals the current date: =Today(). In C3, I would like a function to look at the date in C2 then match that date to the date I have in Column F that starts with 1/1/2015 in F3 going thru 12/31/2015 in F367. Once the date in C2 is matched, the function would return a value from in a range from J3:J367. As an example, using today’s date July 27, 2015, the value would be 208.

I’ve been spinning my wheels for over a week researching the best solution to this. I currently have an Index (array) function that works:

{=INDEX($J$3:$J$367,MIN(IF(($C$2=$F$3:$F$367),MATCH(ROW($J$3:$J$367),ROW($J$3:$J$367)))))}

However, my question with my limited knowledge of the advanced functions, is this the best approach? I’m not sure the advantage or disadvantages versus other potential Lookup and Reference Functions that that are available (e.g., VLOOKUP, etc.).

I would greatly appreciate any thoughts or comments using the current solution.

Reply | Quote

Replies

sbdale,

Here’s another possibility: [noparse]=OFFSET($C$2,MATCH($C$2,$F$3:$F$367,0),7)[/noparse]

Note that the offset references C2 vs C3 because you are working with offsets. If you referenced C3 you would have to subtract 1 from the Match function result.

BTW: I’d advise a Dynamic Range Name for the Date list that way you will never have to change the formula.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Sbdale,

Not sure why you are using an array formula where

=INDEX(F3:J367,MATCH(C2,F3:F367,0),5) in cell C3 would provide the same results (208 in this case).

HTH,
Maud

Reply | Quote

Why not just use =VLOOKUP(C2,F3:J367,5,0) – this seems to much simpler that the other suggested formulae.

Regards,
Maria

Reply | Quote

Hi
or this

=C2-DATE(YEAR(C2),1,1)+1

Then there is =DatedIf function.
=DatedIf(date1, date2, intervalType)

Cheers
G

Reply | Quote

All,

Thank you for your suggestions. I really appreciate you taking the time to provide feedback. All the suggestions appear to be better and a lot easier than the array function that I initially used.

Like I indicated in my original post, I have limited known of advanced functions. So, I reviewed each of your suggestion to ensure I understand how they worked.

My only question is for Geof. Your function (=C2-DATE(YEAR(C2),1,1)+1) works; however, I don’t understand why. The 1,1)+1) is throwing me a curve. If possible, could provide a brief description how the function works or a website that would expand on the functions. I did attempt to some research, but it only confused me more.

Again, thank you for all the suggestions.

Reply | Quote

Hi

..think of it like this, if C2 is 1st January then
C2 – date(year(c2),1,1) would just give you 1st Jan – 1st Jan = zero
..so add +1 at end
i.e. c2 – date(year(c2),1,1)+1 would give you 1 (if the date c2 was 1st Jan) etc etc etc

zeddy
•Satellite Procurement Specialist

Reply | Quote

Hi sbdale
Welcome to the world of date arithmetic.
You need to understand that excel treats dates as serial numbers. (Capt Kirk says star date 42214)
We mere mortals need dates formatted to show years, months and days.

See this postfrom the office support folks at microsoft.

Enter a date in a cell and experiment with the formatting (Ctrl + 1).

Experiment with the datedif() function as well. See this linkfor info.

Cheers
G

Reply | Quote