You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- locating correct row

Advertisement

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

Eric

ANSWER: Eric,

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

=SUMIF(A:A,"2/12/2014",D:D)

now to get the 2255 you would do

=SUMIFS(C:C,A:A,"2/12/2014",D:D,SUMIF(A:A,"2/12/2014",D:D))

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

=SUMIF(A:A,J7,D:D)

=SUMIFS(C:C,A:A,J7,D:D,SUMIF(A:A,J7,D:D))

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.

--

Regards,

Tom Ogilvy

---------- 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"?

Cheers

Eric

Eric,

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

=INDEX(A:D,MATCH(F1,A:A,0)+COUNTIF(A:A,F1)-1,3)

=INDEX(A:D,MATCH(F1,A:A,0)+COUNTIF(A:A,F1)-1,4)

where F1 holds the date to be looked up.

if you want to hard code a date it would be

=INDEX(A:D,MATCH(DATEVALUE("2/12/2014"),A:A,0)+COUNTIF(A:A,"2/12/2014")-1,3)

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.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Damn!! That works, fantastic! I will have to read what you have written a few times before I get the logic. Thanks again! Eric |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.