(vlookup?) formula to return the first - last value from range which meets condition

[nielsb]

Hi there,
I'm looking for 2 (vlookup?) formulas which will return me resp. the first and last value from a range which meets a given condition (illustrated in attachment):
. The 1st formula should return the FIRST value (5) next to the 'dog' entry in column A
. The 2nd formula should return the LAST value (7) next to the 'dog' entry in column A

Anybody an idea how to do this?

Regards Niels

[BadlySpelledBuoy]

Should the first instance of Dog not return 2 rather than 5 as stated above?

BSB

[nielsb]

yes you´re right

[BadlySpelledBuoy]

OK, just thought I'd check.

This for first instance:

Please Login or Register  to view this content.

and this for last instance:

Please Login or Register  to view this content.

BSB

[nielsb]

They seem to work well. One question I still have is why can´t I you the first formula for the 2nd instance as well, whereby changing FALSE for TRUE?

[BadlySpelledBuoy]

That's not what the TRUE/FALSE argument of a VLOOKUP does. It is there to dictate whether or not you're looking for an exact match (FALSE) or a close match (TRUE).

BSB

[nielsb]

I noticed that changing FALSE to TRUE returned a 7.. Should I consider this as a coincidence then?

[BadlySpelledBuoy]

Change "Dog" to "Horse" and still use TRUE. Do you get the expected result then?

BSB

[BadlySpelledBuoy]

Worth pointing out that if the list were sorted into alphabetical order on column A then using TRUE would indeed give you the last instance and using FALSE would give you the first.

BSB