look up X and return a header Y

  • Thread starter Thread starter MMuscat
  • Start date Start date
M

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
 
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
 
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
 
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
 
thanks Biff that explains the lookup formula much better!
I am afraid the help file does not mention this at all!
Thanks again
 
You'rw welcome. Thanks for the feedback!

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

Biff
 
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top