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

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

MMuscat wrote:

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

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/(B3:D3=A10),B1:D1)

Biff

"MMuscat" wrote in message
oups.com...
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

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

Biff wrote:

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/(B3:D3=A10),B1:D1)

Biff

"MMuscat" wrote in message
oups.com...
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

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

Sure....

1/(B3:D3=A10)

(B3:D3=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},B1:D1)

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" wrote in message
ups.com...
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

Biff wrote:

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/(B3:D3=A10),B1:D1)

Biff

"MMuscat" wrote in message
oups.com...
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

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 wrote:

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

Sure....

1/(B3:D3=A10)

(B3:D3=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},B1:D1)

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" wrote in message
ups.com...
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

Biff wrote:

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/(B3:D3=A10),B1:D1)

Biff

"MMuscat" wrote in message
oups.com...
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

You'rw welcome. Thanks for the feedback!

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

Biff

"MMuscat" wrote in message
oups.com...
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 wrote:

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

Sure....

1/(B3:D3=A10)

(B3:D3=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},B1:D1)

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" wrote in message
ups.com...
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

Biff wrote:

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/(B3:D3=A10),B1:D1)

Biff

"MMuscat" wrote in message
oups.com...
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

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!

T. Valko wrote:

You'rw welcome. Thanks for the feedback!

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

Biff

"MMuscat" wrote in message
oups.com...
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 wrote:

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

Sure....

1/(B3:D3=A10)

(B3:D3=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},B1:D1)

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" wrote in message
ups.com...
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

Biff wrote:

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/(B3:D3=A10),B1:D1)

Biff

"MMuscat" wrote in message
oups.com...
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