Multiple (3-way) lookups

[cidgreen]

Apologies if this has been asked before but I couldn't find it having searched the forum, and it is something that has been driving me insane for months.

I need, somehow, to create a three-way lookup to find a value in a spreadsheet.

To put it in context, I have in column A a list of English Premiership football teams. I have in column B dates of each of their matches since 1992. I have in column C a "W", "L" or "D" for the result of the match. And in column D I have a formula calculating the current run of matches without a win (reset to zero if they win, add one to the previous number if they don't win).

Basically, I need to find the current run of games without a win for a particular team. For example, I need to find the current run of matches without a win for Everton (i.e. the value in the 'runs' column which corresponds to both Everton and the more recent date).

This is something I have always needed to do but have always gone about it in a ridiculously long-winded way, making my files way too big.

If anyone could help, or direct me to somewhere that could show me how to do this, that would be a massive help to me.

Thanks in advance if anyone can help,
Chris.

 

[s-o-s]

On 2002-08-27 03:15, cidgreen wrote:
Apologies if this has been asked before but I couldn't find it having searched the forum, and it is something that has been driving me insane for months.

I need, somehow, to create a three-way lookup to find a value in a spreadsheet.

To put it in context, I have in column A a list of English Premiership football teams. I have in column B dates of each of their matches since 1992. I have in column C a "W", "L" or "D" for the result of the match. And in column D I have a formula calculating the current run of matches without a win (reset to zero if they win, add one to the previous number if they don't win).

Basically, I need to find the current run of games without a win for a particular team. For example, I need to find the current run of matches without a win for Everton (i.e. the value in the 'runs' column which corresponds to both Everton and the more recent date).

This is something I have always needed to do but have always gone about it in a ridiculously long-winded way, making my files way too big.

If anyone could help, or direct me to somewhere that could show me how to do this, that would be a massive help to me.

Thanks in advance if anyone can help,
Chris.

Chris,

I'm not Exactly sure what the output you want is but I have come up with 2 array formulas type the details in as before(Do not enter the curvy brackets) but press Ctrl + Shift + Enter. To enter the formula.

I have used 2 cells, the 1st calculates the Date of the last win

The second calculates the number of matches since the last win.

Please see the Table below.

Hope this helps
Sean

Book2
	A	B	C	D	E	F	G
1	Team	Date	W/L/D
2	EV	01/01/02	L			LastWin	NoMatches
3	LIV	01/01/02	W
4	LIV	02/01/02	W		EV	06/01/02	4
5	EV	02/01/02	L
6	EV	03/01/02	L
7	EV	04/01/02	W
8	EV	05/01/02	L
9	EV	06/01/02	W
10	EV	07/01/02	L
11	EV	08/01/02	L
12	EV	09/01/02	L
13	EV	10/01/02	L
Sheet1

If this is what you are wanting you can combine the 2 cells into one. but thought I would check 1st.
_________________<MARQUEE/><A HREF= "http://website.lineone.net/~s-o-s/Index.html">

</MARQUEE>
This message was edited by s-o-s on 2002-08-27 05:25

 

[cidgreen]

Thanks Sean, that's excellent.

I'm almost there, at least in terms of understanding what you've suggested. But I've copied out your example into a blank sheet and I can't seem to get it to work.

If I choose EV for the selection in cell E4, it returns zero for both F4 and G4.

Yet if I enter LIV in E4, it returns the last date in the list (10/01/2002) in F4 and zero in G4.

I have checked that everything has been entered correctly, and everything is in the right place, but I can't seem to sort it out. Am I doing something wrong?

Once again, thanks for your help,
Chris.

 

[s-o-s]

Chris,

Send me a PM with your email address and I'll Email the sheet through to you.

It works correctly for me...

Or send me your file and I'll look through later today/tonight

Hope this helps.

 

[cidgreen]

Thanks Sean that's brilliant, I've just sent you the sheet I'm trying to sort out.

Thanks very much for your help,
Chris.