h and v lookup together

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336
hig uys, it's the pain in the *** again. hehe.
i am once again trying to make my workbook more entertaining. mroe auto stuff so i can be lazy.
in my past posts i learned how to correct the #div/0 so that my vlookups would work. and when i use an if statement over and over it now works to give me the results of my teams score on another sheet.
what i want to do know is make vlookup do this.
in sheet1 i have cellsb32 and b33 with the name of each team. example boston and new york. and in cells n32 and n33 i have their final scores. so if the game is 7-6 for example i will do an if statment that if cell n32 is greater than n33 than 1 if not then 0. but how can i make v and h lookup do this for me?

i can do a vlookup for the score but i want the 1 or 0 answer.

thanks in advance for your help
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why do you need lookups?

Wouldn't this do what you need?

=--(N32>N33)
 

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336
well because if you can picture what i am doing you would realize that it's a must.
i am doing stats for fantasy baseball leagues, 10 teams go against each other 18 times during the year. using a schedule format i am able to set up who plays who each week and i enter that info manually. now i am trying to set up a standings page. and instead of manually changing the chart after each week the results i want to have it calculate itself. i can take my time and say if team team beat this team then give them a win and so forth but i like to make things hard for myself at the start so i can sleep easy afterwards. if i could do a vlookup to do this i would only need to make a macro for sorting. which i am learning in my macro book.

what i want to know is, is there a way to put h and v lookup together to make then not only get cells in the same row but down one column. so reference cell is in a12 for example and brings back j12, but i want to compare it to j13 to find out who had the higher score.

if this makes sense let me know
if not i will try to explain some other way


thanks
taz
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
I don't follow you project 100%. But it sounds like you should be using index and match instead of hlookup and vlookup.

For example:
Cell E1 contains:
=INDEX(B1:C7,5,2)

Notice, it returns the 5th row, 2 column within the range B1:C7
In this case that's 9
Book1
BCDE
1NY79
2Boston8
3Philly8
4Cincy3
5Baltimore9
6Atlanta8
7Phoenix2
Sheet1
 

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336

ADVERTISEMENT

ok so looking at your chart will help me explain
take nyy against boston for example
boston wins 8-7, so they score 1 win and nyy one loss,
this is shown in another worksheet in my workbook.
so in the standings nyy would be 0 and 1 and bos would be 1 and 0, and i need to do this 18 weeks over all reporting to the same cell to give me a gradual increase. so at the end of the road we have a 9 and 9 team a 10 and 8 team and so on a so forth.

does this help
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Assuming you keep all of the scores in a continus column as shown on this sheet. Add the formula in column E. (To every other row, or else you'll double count wins)

=INDEX(B1:B2,MATCH(MAX(C1:C2),C1:C2,0),1)

That is, Place the above formula in E1, copy E1 and E2, paste down

Then use the CountIF function to create the table with the cumulative number of wins for each team.

As of right now, ties go to the first time listed in the match up.

How about something like the following:
Book1
ABCDEFGH
1NY7BostonTeamWins
2Boston8NY0
3Philly8PhillyBoston2
4Game 1Cincy3Philly2
5(week 1?)Baltimore9BaltimoreCincy0
6Atlanta8Baltimore1
7Phoenix2FloridaAtlanta1
8Florida7Phoenix0
9NY7BostonFlorida2
10Boston9
11Philly10Philly
12Game 2Cincy3
13(week 2?)Baltimore4Atlanta
14Atlanta8
15Phoenix2Florida
16Florida8
Sheet1
 

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336

ADVERTISEMENT

ok this is assuming that we look at each cell to be compared to each other, your b1 and b2 and c1 and c2 .
so this is not able to look up by itself.
but it does give me and alternative to using
if(c1>c2,1,o)
and then the vise versa for for the other team with c2>c1,1,0
will try it out but still not accomplishing what i thought about. if i simplify my request, can we try to use vlookup to bring back that if statement i gave you. so let's say for this example
vlookup boston within this example and compare it to new york andbring me back the answer

one last thing can this formula you used the index, can it be stringed together 18 times, so week 1 results +week 2 results and so forth
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
so this is not able to look up by itself.

Not sure what you mean by look up itself.

I'm also not sure what if statement you are refering to. (or what its function is)


I'm also not sure of the geography of your Workbook, where week1 results are, week2 etc. Are they each on separate worksheets?


As I see it, each game is a match up between two teams, One team wins, and one team loses. Column E, will tell you who one that game.

If you put week 2 games, under the week 1 match ups as I did, and week 3 under that etc.

SumIfing column E will take care of it. There is not need for index + index +...

I must go to bed, I will check back tomorrow.
 

taz23340

Active Member
Joined
Jun 11, 2003
Messages
336
hi again teach.
we have a 10 team league so each week we have five games between two teams
each week is on it's on worksheet
giving me 18 worksheets
on the 19th sheet i have standings page
which i calculate the winning % of each team so that i know who is in 1st 2nd....
i do this by taking the avg of the winning and losing by the total amount of games and then sort the table
kind of like any standings results for any sport in any newspaper
team# 1 is team with best %
so i calculate for each team their wins and losses
i do this by adding 18 if's together
if boston score is greater than nyy score in week one then they get one if not 0 in the wins column
then i do the inverse for losses, with of boston is less than nyy in week one then 1 if not then 0
so i would end up with 1win 0 loss for bos in week one
so far what i do is when i have the schedule for all the weeks entered i go and manually do this,
so boston plays nyy week 1 then boston play seattle week 2, so on so forth. and i add them all to gether to give me the results of all the wins and all the losses. for each team
does this make more sense?
what i was trying to see is if there is a way to use a formula to find boston in each week and bring me back the results in each week without telling it what cell it's in, i have my sheets setup the same for all the 18 weeks and i know that from "boston game results" which is their score. that the score appears 12 columns after. and the team it goes against is either under or above it depending on who it is against, all sorted alphabetically. so when i play anaheim by team, boston, is below it when i play seattle it is above.

let me know if this helps at all
or if it just cinfused you even more

thanks
taz

p.s. i still can't use the pictue html maker as my work server does not allow downloads, server blocks me, friggin crap. lol
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Just Copy my Column E, and put it next to each of the games on all 18 sheets.


Then in your summary table( presumably on your summary sheet)

=CountIf(Week1!E$1:E$10,B1)+
CountIf(Week2!E$1:E$10,B1)+
CountIf(Week3!E$1:E$10,B1)+
CountIf(Week4!E$1:E$10,B1)+...


Assuming Column A1:A10 contain the names of each of your 10 teams.
Change Week1!, Week2! etc to the names of your sheets.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top