Excel/locating correct row
QUESTION: Hi Tom
I have Excel 2007.
My problem is on my sheet as shown I have 5 dates (there are more dates on my spreadsheet). On the 2/12/2014 I have two dates the same but on the first of the 2 dates the third column is blank. I want to ignore the date with the blank third column and jump to the date that has all the numbers in place. VLOOKUP works fine with one lookup criteria but no good for what I want to do.
Note: If there are two dates the same there is always no number in the third column for the first date.
1/12/2014 1100 2200 67
2/12/2014 1100 22
2/12/2014 1100 2255 55
3/12/2014 1100 2150 72
4/12/2014 1100 1255 103
How do I pick up the "55" in the fourth column which is associated in this case with the second 2/12/2014 and the "2255"?
I hope my question is clear enough.
Have a great Christmas
for this case where you are retrieving a numeric value, then you can use Sumifs
Assume you dates are in column A
to get the 55 you would do
now to get the 2255 you would do
so this applies two conditions - that column A has the date and column D has the previously retrieved value.
Now if you had the date 2/12/2014 in cell J7 (as an example), you could just refer to that cell in these formulas
Note that the second formula uses the new SUMIFS formula since I am specifying two conditions. This was introduced in Excel 2007.
So this assumes that you only have at most two entries with the same date which is basically what you stated.
Let me know if this doesn't work because your data is not actually as you have portrayed it or for some other reason. (I have tested it with the data you show and it worked for me)
Also, these should work for the entries that have only one entry per date.
---------- FOLLOW-UP ----------
QUESTION: Hi Tom
Thanks for getting back so soon.
I am a little confused with your answer as your solutions is returning a zero when I try it.
I have redone the columns again
1/12/2014 1100x 2200x
2/12/2014 1100x no
2/12/2014 1100x 2200x yes
3/12/2014 1100x 2200x
4/12/2014 1100x 2200x
I am only wanting to retrieve in this case the item in column "d" in the second date of 2/12/2014. The only thing to identify the difference between the first and second entry of 2/12/2014 is that there is nothing in column "c". With vlookup it will locate whatever is in column "d" in the first of the 2/12/2014. How do I make a lookup with 2 criterias allowing me to find the (in this case)the "Yes"?
This medium doesn't preserved layouts very well. So even though you said the third column is blank, you picture appeared to show the blank being in the 4th overall column and I wrote my formula accordingly.
You also have gone to great lengths to make sure you have no numbers. so my revised formula would use Index and match.
=INDEX(A:D,MATCH(DATEVALUE("2/12/2014"),A:A,0)+1,3) will return the value from column C
=INDEX(A:D,MATCH(DATEVALUE("2/12/2014"),A:A,0)+1,4) will return the value from column D
Note that the last argument (3 or 4) determines which column in the Index range (A:D) will return the value. This will work regardless of the form of the value returned since it only looks at column A.
As written, this would only work for dates that are duplicated and assumes the dates are in adjacent cells. To make it work with both dates that only appear once dates that appear twice, we will use the countif formula to replace the +1
where F1 holds the date to be looked up.
if you want to hard code a date it would be
This time I make no notice of the blank cell but use the fact that if there are two dates you always want the return from the second date. You did state that that is the case.
All formulas tested with your data and worked fine for me.