Continue looking in a table if 1st matched cell is blank

**patchulee** · 07-06-2011, 08:42 AM

Hi,

Let's see if I can explain my problem and if you can help me !

I have a table such as:

Column A = Partners name
Column B = Date
Column C = Last Entrance Indicator (cell = "" when the partner does not appear later in the table or = "YES" if it appears later)

The date in column B is only showing up for the last entrance of a partner (eg: if partner X appears in row 3, 8 and 10, date will only appear in row 10)

So for instance, my table will look like

Partner 1 ; "" ; YES
Partner 2 ; 12/03/11; ""
Partner 3 ; 25/04/11; ""
Partner 1; 10/06/11; ""

On a separate sheet, I want to return the date for a specific partner.

In this case, I want the date for partner 1.
Using a vlookup doesn't work because the formula stops at the first instance (row 1) and the formula will return a blank.

What formula could I use to say: if the first date cell for this partner is blank, keep looking in the table and return the date? (note: in this example partner 1 is only repeated once, but it might be repeated several times)

Thank in advance for the help.

**NBVC** · 07-06-2011, 08:51 AM

=INDEX(Sheet1!B1:B100,MATCH(TRUE,INDEX((Sheet1!A1:A100=A1)*(Sheet1!B1:B100<>""),0),0))

Where A1 contains the Partner to lookup in sheet1, A1:A100... adjust ranges and sheetnames to suit.

**CANAPONE** · 07-06-2011, 08:56 AM

Hi, maybe you could try (column A partner, column B dates)

=LOOKUP(2,1/((A2:A100="Partner1")*(B2:B100<>"")),B2:B100)

or simplier

=LOOKUP(2,1/(A2:A100="Partner1"),B2:B100)

Regards

**NBVC** · 07-06-2011, 09:00 AM

The difference between the first 2 suggestions given is that mine will find the first non-blank and Capanone's will find the last entry where Partner matches.

Depending on your setup they could be the same result.. but if you have multiple dates or there are blanks at the last matching partner, then the latter will give that last date or blank (or other).

**patchulee** · 07-06-2011, 09:50 AM

Hi,

Thanks !

I will try with the "short" one ...
For some reason , I don't manage to get the "INDEX" one to work .. it returns a N/A. I am probably doing something wrong but cannot figure what ....
I guess that the last one should be fine as only the last entrance of the partner will have a date.

Thanks again.

**NBVC** · 07-06-2011, 10:01 AM

It may be because you lookup value doesn't exactly match your table match value... make sure there are no extra spaces.

Another formula that might work if you only have one date per partner is:

=SUMIF(Sheet1!A1:A100,A1,Sheet1!B1:B100) adjusting to suit

You will need to format result as date....