Checking the lower row of vlookup()

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ETLahrs - 03 Mar 2008 21:31 GMT

I am trying to see if there is a way to check a lower row after vlookup has
found the string in the first column. Example:

Year Event Length
1995 A 10
1995 B 56
1995 C 15

How would I search on another sheet to see if year "1995" had event "C", and
if so, display the length. This is a long spreadsheet with many items in the
first column and the second column isn't always in the number of entries.

Thanks For Any Info.
Edward

Reply to this Message

T. Valko - 03 Mar 2008 22:03 GMT

*Maybe* this:

=SUMPRODUCT(--(Year=1995),--(Event="C"),Length)

Signature

Biff
Microsoft Excel MVP

>I am trying to see if there is a way to check a lower row after vlookup has
> found the string in the first column. Example:
[quoted text clipped - 12 lines]
> Thanks For Any Info.
> Edward

Reply to this Message

Fred Smith - 03 Mar 2008 22:13 GMT

One way is to create a helper column that's =A2&B2, then Vlookup '1995C'.

Another way is to use Sumproduct, as in:

=sumproduct(--(a2:a4=1995),--(b2:b4="C"),c2:c4)

Both of these will work as long as the combination of A and B is unique.

Regards,
Fred.

>I am trying to see if there is a way to check a lower row after vlookup has
> found the string in the first column. Example:
[quoted text clipped - 12 lines]
> Thanks For Any Info.
> Edward

Reply to this Message