Look up two values

[wladoD]

Hi Guys,

Can you please help me to solve this challenge.
I have table with played matches. I have array formula to show last 10 dates when team played.


Now I need to find out based on date and team name what was the outcome of the result. (out1) or (out2)

Thank you very much

[shivya]

Two lookup values
=VLOOKUP(VLOOKUP(O15,C2:M7,3,0),E2:M7,5,0)

[wladoD]

HI,

Thanks for your quick reply.
But it has to look up the date and the team name please.

Thank you

[MarvinP]

Hi WladoD

Put this formula in P7 and pull it across and down.

Please Login or Register  to view this content.

Home vs Away Win Loss Lookup.xlsx

[Glenn Kennedy]

In P7, copied across and down (ordinary formula), which avoids the use of volatile functions which may be slow:

=IFERROR(IFERROR(INDEX($E$2:$E$7,MATCH(1,INDEX(($C$2:$C$7=$O7)*($A$2:$A$7=P2),0),0)),INDEX($I$2:$I$7,MATCH(1,INDEX(($L$2:$L$7=$O7)*($A$2:$A$7=P2),0),0))),"")

[wladoD]

Hi,

Thank you, waw bit of complex formula here.
I would like to ask you is it possible to to use array formula from P2 to add to your formula?
To make everything happen in one cell??

Thank you

Can you please explain "C", "I", "E" ??

[Glenn Kennedy]

In Ireland... CIE is Córas Iompair Éireann: the State Public Transportation system!!

In Excel, they are references to columns C, I and E which are used in MarvinP's volatile formula.

[MarvinP]

Hi wladoD,

Your data is not good for Excel. If the team is Away you look in column E (to the right one column) but if it is Home you look in column I (two columns to the left). So my formulas looks up the date and if it is the away team it finds the answer in column E else looks at column I.

If you had a better example with many teams playing on the same date, my formula might not work.

When you ask for an Array formula and say my answer is complex, I

Also - I don't know what you mean with "To make everything happen in one cell??"

[wladoD]

Hi,

The data is set for NHL in USA so thats why away vs home.
Lots of dame wont be a problem as it looks at minutes too.

I only asked if you could incorporate this formula into yours
=IFERROR(LARGE(IF(OR($C$2:$C$7=$O2,$L$2:$L$7=$O2),$A$2:$A$7),COLUMNS($P2:P2)),"") - This will give you last date of the match

In your formula you look for this value to match.
I was wondering if you could incorporate it in one formula please.

Thank you

[wladoD]

Can you please suggest how should I have the data set if you say this way it is not good for excel please.

[Glenn Kennedy]

wladoD. Please address your remarks to an individual. Otherwise we are left guessing who you are talking to.

Have you looked at the formula in Post 5 yet?

[wladoD]

Hi Glenn,

Yes I did look at it and tested it.
It works very well.
Is it possible to incorporate this formula which is looking for last date of the game into your please?

Please Login or Register  to view this content.

Thank you

[Glenn Kennedy]

You mean like this:

=IFERROR(IFERROR(INDEX($E$2:$E$7,MATCH(1,INDEX(($C$2:$C$7=$O7)*($A$2:$A$7=LARGE(IF(OR($C$2:$C$7=$O7,$L$2:$L$7=$O7),$A$2:$A$7),COLUMNS($P7:P7))),0),0)),INDEX($I$2:$I$7,MATCH(1,INDEX(($L$2:$L$7=$O7)*($A$2:$A$7=LARGE(IF(OR($C$2:$C$7=$O7,$L$2:$L$7=$O7),$A$2:$A$7),COLUMNS($P7:P7))),0),0))),"")

I don't think this is a good idea... In a week from now you will not have a clue how it works. Youwill find it impossible to maintain....

I forgot to add: it is now an array formula and needs CTRL-SHIFT-ENTER

[MarvinP]

I won't need to wait a week to be clueless on how that monster formula works

I think the OP wants to only show the last 10 matches between the two opponents

My advice would be to restructure the data and have two rows for each game. The column heads would be:
Date, Team, Away/Home, Goals, Win/Loss/Tie, RT/OT, Status
The above table structure would be much better for Excel and doing stats but I also understand that it is presented like the OP posted..

[Glenn Kennedy]

Me neither....

[wladoD]

I have tested it.
It works I just dont understand when I added two new teams why the latest results are not showing under the 10 (column P)

[wladoD]

I would like to show
LAST 10 Matches of the team in the league.
We were able to get the last 10 dates
We were able to combined it with the match outcome
Now what I need to do is concatenate and countif the values i need.

Can you please explain two rows for each game?

[Glenn Kennedy]

I told you it wasn't a good idea. It failed because YOUR array formula (returning the dates) and which I simply copied into my formula, was incorrect.

[wladoD]

I agree with you it is huge and scary formula.
I guess I dont have much of a choice.

Big Thanks

[Glenn Kennedy]

We got there in the end, though...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[MarvinP]

Hey Wlado,

You asked for a 2 rows per game table format. See the attached for what I mean. You'd need to enter two rows for each game, one for the home and another for the away team.

If you entered your data like the attached, you could do much better analysis and formulas with it.

Home vs Away Win Loss Lookup Better.xlsx

[wladoD]

Hi Marvin,

I have more than 1,300 games in the format.
Which is played between two players.
Thats why I need formulas as I ll be embedding it the website.

Thank you

[Glenn Kennedy]

You sent me a PM overnight. Yep. I see the problem. Fixed now (I hope!!). Array formula.

=IFERROR(IFERROR(INDEX($E$2:$E$7,MATCH(1,($C$2:$C$7=$O7)*($A$2:$A$7=LARGE(((($C$2:$C$7=$O7)+($L$2:$L$7=$O7))*$A$2:$A$7),COLUMNS($P:P))),0)),INDEX($I$2:$I$7,MATCH(1,($L$2:$L$7=$O7)*($A$2:$A$7=LARGE(((($C$2:$C$7=$O7)+($L$2:$L$7=$O7))*$A$2:$A$7),COLUMNS($P:P))),0))),"")

[wladoD]

Hi Glenn,

Yes, now it works like charm even on my 1300 values database.

Thank you very much.

[Glenn Kennedy]

Great!! The last mistake was mine...