look up X and return a header Y

[MMuscat]

Hi,

I have a list of referees whereby some names could be repeated and I
want to find the corresponing date and match a referee has last
officiated in.

for example:
Match: M vs S | S vs X | G vs K
Date: 20/06 | 21/06 | 22/06
Ref: Mario | Peter | Mario

I want to search the last match Mario has refereed in and return G vs
K.

I tried using offset, match and lookup but I am not really familiar
with these formulas sa I rarely use them.

Regards,

Mark

 

[MMuscat]

After trying all combinations of formulas I have given up and used a
pivot table!
M

 

[Biff]

After trying all combinations of formulas I have given up and used a

pivot table!

No need to torture yourself!

...............B..............C..............D
1........M vs S......S vs X......G vs K
2.........20/06.......21/06........22/06
3.........Mario.......Peter.........Mario

A10 = lookup value = Mario

=LOOKUP(2,1/(B33=A10),B11)

Biff

 

[MMuscat]

hello Biff thanks...

could you explain the use of 1/ in the lookup vector?

I completly ommited that!

Anyway now i have two reports I can compute (pivot table and this) so
it helps to double check

M

 

[T. Valko]

could you explain the use of 1/ in the lookup vector?

Sure....

1/(B33=A10)

(B33=A10) will return an array of TRUE or FALSE

{TRUE,FALSE,TRUE}

Dividing by 1 will coerce those logical values into numbers:

1/(TRUE,FALSE,TRUE}

Since FALSE will coerce into 0 that will result in a #DIV/0! error:

1/(1,#DIV/0!,1}

So now the formula evaluates to this:

=LOOKUP(2,{1,#DIV/0!,1},B11)

The way that Lookup works is if the lookup_value (2) is greater than any
number in the lookup_vector {1,#DIV/0!,1}
the result of the formula will be the LAST value that is less than the
lookup_value. Since 2 is greater than any value in the lookup_vector the
formula returns the value from the result_vector that corresponds to the
last number in the lookup_vector.

So, the result is the value in D1 (result_vector) that corresponds to the
last 1 in the lookup_vector:

......B1..........C1..........D1
.......1.......#DIV/0!.......1

Hope that makes sense!

Biff

 

[MMuscat]

thanks Biff that explains the lookup formula much better!
I am afraid the help file does not mention this at all!
Thanks again

 

[T. Valko]

You'rw welcome. Thanks for the feedback!

Yeah, HELP gives only the most basic info is usually not much........help!

Biff

 

[MMuscat]

I have already applied this formula to 3 other instances since the
question... a case of deja vu... theres a formula and cause u don't
know about it, you just don't use it! Excel!