Excel lookup question 1

[chevyv8]

This has been driving me mad all day, there must be some way to do this but I can't work it out.

Is there some way I can lookup data in the 1st non blank cell in column C if there is a match in column A?

In the example below I want to return the 1st person who has product1 in any colour

Product Colour Who
product1 white
product2
product1 pink mike
product3
product1 blue

 

[SkipVought]

hi,

What is the structure of your lookup table.

Please post a sample of your lookup table that relates to your posted example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chevyv8]

I hope this is a better description

A B C
1 Product Colour Who
2 product1 white
3 product2
4 product1 pink mike
5 product3
6 product1 blue

If I use the following formula the answer would be 0
=VLOOKUP("product1",A2:C6,3,FALSE)
But I am looking for is a formula that would ignore the colours (column B) and return either:

"mike" because it was the 1st occasion where "product1" had been found and column C of the same row was not blank.
or
The row number so that I could use it with an Offset command

 

[SkipVought]

WHAT TABLE is it that you lookup the product and color and get a NAME?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

This array formula ( entered using Ctrl-Shift-Enter ), will return the row number where the first person has product1:

=MIN(IF(C2:C6<>"",IF(A2:A6="product1",ROW(A2:A6))))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[chevyv8]

Thanks GlennUK, it works a treat.

 

[GlennUK]

My pleasure.

Cheers, Glenn.

Beauty is in the eye of the beerholder.