Vlookup

[kreatiff]

Ok - I'm at that pulling out hair stage and I haven't got that much left!

I'm using the formula below to determine whether a team has Won Drawn or Lost a match based upon the result.

=IF($F4="","",(IF($F4<$C4,"W",(IF($F4>$C4,"L",(IF($F4=$C4,"D","")))))))

The result of the formula is either, W, D, or L.

I have created a small table that incorporates a list of the teams on the left and three additional columns with a heading W, D, L respectively. Using VLOOKUP add the following formula alongside each team under the appropriate heading as below:

=VLOOKUP(H4,home,2)

H4 = Team name
Table array = home
Number of columns to move right = 2

This gives a result of #N/A.

What I want it to do is simply add the number of instances the club name appears in my table array and count or add the number W, D, L it sees.

Driving me nuts at the moment - so any pointers appreciated.

Many thanks in advance

J

[Les]

I'm not sure how my example will show up, but here goes:

Column G has your formula in it.
Column H (in the cell under Team is where you type the team
for which you want to count results.

The formulas under the wins/draws/losses cells are array formulas. (Press
CTRL-SHFT-ENT
when typing them in rather than just pressing ENT)

You'll have to imagine the rows numbers:
win formula: =SUM(IF($E$4:$E$12=$H$2,IF($G$4:$G$12="W",1,0)))
draw formula: =SUM(IF($E$4:$E$12=$H$2,IF($G$4:$G$12="W",1,0)))
loss formula: =SUM(IF($E$4:$E$12=$H$2,IF($G$4:$G$12="W",1,0)))

If you have entered the formual correctly it will be enclosed in curly
braces {}.

Example:
-----------
columns
C D E F G
H
=================================================
Team:
BB
Team A Score Team B Score Result Wins Draws Losses
AA 5 BB 4 W 2 3 1
AA 10 BB 10 D
AA 15 BB 16 L
AA 5 CC 6 L
AA 10 BB 10 D
AA 15 BB 14 W
AA 5 BB 5 D
AA 10 CC 11 L
AA 15 CC 12 W

--
Les Torchia-Wells


"kreatiff" wrote:

>
> Ok - I'm at that pulling out hair stage and I haven't got that much
> left!
>
> I'm using the formula below to determine whether a team has Won Drawn
> or Lost a match based upon the result.
>
> =IF($F4="","",(IF($F4<$C4,"W",(IF($F4>$C4,"L",(IF($F4=$C4,"D","")))))))
>
> The result of the formula is either, W, D, or L.
>
> I have created a small table that incorporates a list of the teams on
> the left and three additional columns with a heading W, D, L
> respectively. Using VLOOKUP add the following formula alongside each
> team under the appropriate heading as below:
>
> =VLOOKUP(H4,home,2)
>
> H4 = Team name
> Table array = home
> Number of columns to move right = 2
>
> This gives a result of #N/A.
>
> What I want it to do is simply add the number of instances the club
> name appears in my table array and count or add the number W, D, L it
> sees.
>
> Driving me nuts at the moment - so any pointers appreciated.
>
> Many thanks in advance
>
> J
>
>
> --
> kreatiff
> ------------------------------------------------------------------------
> kreatiff's Profile: http://www.excelforum.com/member.php...o&userid=37732
> View this thread: http://www.excelforum.com/showthread...hreadid=573223
>
>

[Les]

My example did not come through very well.
Feel free to contact me at [email protected] for a better sample.
--
Les Torchia-Wells


"kreatiff" wrote:

>
> Ok - I'm at that pulling out hair stage and I haven't got that much
> left!
>
> I'm using the formula below to determine whether a team has Won Drawn
> or Lost a match based upon the result.
>
> =IF($F4="","",(IF($F4<$C4,"W",(IF($F4>$C4,"L",(IF($F4=$C4,"D","")))))))
>
> The result of the formula is either, W, D, or L.
>
> I have created a small table that incorporates a list of the teams on
> the left and three additional columns with a heading W, D, L
> respectively. Using VLOOKUP add the following formula alongside each
> team under the appropriate heading as below:
>
> =VLOOKUP(H4,home,2)
>
> H4 = Team name
> Table array = home
> Number of columns to move right = 2
>
> This gives a result of #N/A.
>
> What I want it to do is simply add the number of instances the club
> name appears in my table array and count or add the number W, D, L it
> sees.
>
> Driving me nuts at the moment - so any pointers appreciated.
>
> Many thanks in advance
>
> J
>
>
> --
> kreatiff
> ------------------------------------------------------------------------
> kreatiff's Profile: http://www.excelforum.com/member.php...o&userid=37732
> View this thread: http://www.excelforum.com/showthread...hreadid=573223
>
>

[Debra Dalgleish]

You could create a pivot table, based on your home table, to summarize
the data. There are instructions and links here:

http://www.contextures.com/xlPivot01.html

When you create the pivot table, put team name in the row area, Win/Lose
in the Column area, and another copy of team name in the data area,
where it will show as Count of Team.


kreatiff wrote:
> Ok - I'm at that pulling out hair stage and I haven't got that much
> left!
>
> I'm using the formula below to determine whether a team has Won Drawn
> or Lost a match based upon the result.
>
> =IF($F4="","",(IF($F4<$C4,"W",(IF($F4>$C4,"L",(IF($F4=$C4,"D","")))))))
>
> The result of the formula is either, W, D, or L.
>
> I have created a small table that incorporates a list of the teams on
> the left and three additional columns with a heading W, D, L
> respectively. Using VLOOKUP add the following formula alongside each
> team under the appropriate heading as below:
>
> =VLOOKUP(H4,home,2)
>
> H4 = Team name
> Table array = home
> Number of columns to move right = 2
>
> This gives a result of #N/A.
>
> What I want it to do is simply add the number of instances the club
> name appears in my table array and count or add the number W, D, L it
> sees.
>
> Driving me nuts at the moment - so any pointers appreciated.
>
> Many thanks in advance
>
> J
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

[kreatiff]

Thanks to both of you to responding to my plea for help...

Shouldn't I be using VLOOKUP ? and is it possible to SUM or COUNT using VLOOKUP ?

-----------

I'll have a close look at your suggestions :-)

[kreatiff]

Les

Its so easy to forget the basic stuff sometimes, thanks for your effort. I can see how this works... let you know how it goes.

[Debra Dalgleish]

A VLOOKUP formula returns one value from a table, so it wouldn't sum or
count the values.

Another option is to use SUMPRODUCT, as described here:

http://www.contextures.com/xlFunctio...tml#SumProduct

substituting cell references for the typed values.

kreatiff wrote:
> Thanks to both of you to responding to my plea for help...
>
> Shouldn't I be using VLOOKUP ? and is it possible to SUM or COUNT using
> VLOOKUP ?
>
> -----------
>
> I'll have a close look at your suggestions :-)
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

[kreatiff]

A further update:

The design has moved on as I wasn't able to find a solution to use VLOOKUP to count or sum multiple cells:

I've now created columns with the team name at the top of the spreadsheet going left to right. Underneath each team I have listed Week 1, Week 2 etc... going down the page, with alongside the result W, D, or L. At the bottom I have used the COUNT function to sum the total number of draws, wins or losses.

I'm now having a problem - and really shouldn't be - with a straight forward IF statement. As LES offered earlier with his suggestion... using the following should do/say this:

If range A12:A17 equals "TEAM NAME" then VLOOKUP L2 (which is the TEAM NAME) in table array A12:B17 select the value in the second column and return nothing if not found.

=IF(A12:A17="TEAM NAME",VLOOKUP($L$2,A12:B17,2,0),"")

However the result is always a blank cell, as it doesn't appear to recognise the TEAM NAME.

[kreatiff]

Debra

Thanks for your SUMPRODUCT option, I must try this and get back to you...

Cheers

J