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

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

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),MATC H(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.

2. sbdale,

Here's another possibility: =OFFSET(\$C\$2,MATCH(\$C\$2,\$F\$3:\$F\$367,0),7)

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

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

sbdale (2015-07-28)

4. 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

5. ## The Following User Says Thank You to Maudibe For This Useful Post:

sbdale (2015-07-28)

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

Regards,
Maria

7. ## The Following User Says Thank You to simmo7 For This Useful Post:

sbdale (2015-07-28)

8. Hi
or this

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

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

Cheers
G

9. ## The Following User Says Thank You to geofrichardson For This Useful Post:

sbdale (2015-07-28)

10. 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.

11. 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

12. ## Date arithmetic to find day number

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 post from 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 link for info.

Cheers
G

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•