Help with a lookup formula

Acallys · Nov 22, 2013

I've created this spreadsheet to record my stats for playing the game Hearthstone. In the columns T, U, V and W I have my class vs class statistics that I've just started recording. The idea behind this is to have it correlating to a table that shows percentages in rows 33-42 using columns A-J. You can see that I have only recorded one stat so far with this which is Shaman vs Mage with a 1 - 0 win rate. V and W will always either be a 1 or a 0 depending on who won the game as they are best of ones.

So the trouble I'm having was to turn the T-W and get a percentage total in the lower table. The only way I could think of that was to have a step in between which is further right on the spreadsheet, starting from AA and onwards. Then the bottom table takes it from there and turns it into a percentage. If there is an easier way to do that to not have all that stuff convoluting the spreadsheet, that would be great to know.

Above that table is the last thing that I'm working on. Underneath the "TOP 5" in cells A-C and rows 27-31 I'm wanting to do the last table. The idea is to have the class that I'm playing in A (ex. Shaman from the one statistic I have recorded), and the played against class in B, and the percentage winrate in C. I tried to do an HLOOKUP and VLOOKUP formula taking information from the table below, but I couldn't get it to work. I can't think of a way of getting this to work as there is too much info to do a nested IF function. I also don't know how to get it to read the information and place the played and played against class into A and B respectively.

Your help for these formulae would be much appreciated, thanks in advance!

TL;DR - Trying to get cells A-C, 27-31 filled with: A = class played, B = class played against, C = winrate %. Used by taking the information from the table below, which takes the info from cells AA and onwards to the right, which takes info from columns T-W.

https://mega.co.nz/#!yowm1aKZ!ZlLRySDRWDiwJoTv6HmQCV_Dn6dTTltg7qbRMoSCdCA - link to the spreadsheet

oldbrewer · Nov 23, 2013

						played	won	%won
Shaman	Hunter	1	ShamanHunter	1	ShamanShaman	0	0	n/a
Priest	Paladin	0	PriestPaladin	2	ShamanPriest	0	0	n/a
Mage	Warlock	1	MageWarlock	3	ShamanMage	0	0	n/a
Hunter	Paladin	0	HunterPaladin	4	ShamanHunter	1	1	100.0
Paladin	Rogue	1	PaladinRogue	5	ShamanPaladin	0	0	n/a
Warlock	Warrior	1	WarlockWarrior	6	ShamanWarlock	0	0	n/a
Paladin	Rogue	0	PaladinRogue	7	ShamanRogue	0	0	n/a
Rogue	Druid	1	RogueDruid	8	ShamanWarrior	0	0	n/a
Warrior	Warlock	0	WarriorWarlock	9	ShamanDruid	1	1	100.0
Rogue	Mage	1	RogueMage	10	PriestShaman	0	0	n/a
Druid	Warrior	1	DruidWarrior	11	PriestPriest	0	0	n/a
Warlock	Warrior	0	WarlockWarrior	12	PriestMage	0	0	n/a
Mage	Rogue	1	MageRogue	13	PriestHunter	0	0	n/a
Warrior	Hunter	0	WarriorHunter	14	PriestPaladin	1	0	0.0
Warrior	Paladin	1	WarriorPaladin	15	PriestWarlock	1	0	0.0
Rogue	Warlock	1	RogueWarlock	16	PriestRogue	0	0	n/a
Mage	Paladin	0	MagePaladin	17	PriestWarrior	0	0	n/a
Mage	Rogue	0	MageRogue	18	PriestDruid	0	0	n/a
Paladin	Warrior	0	PaladinWarrior	19	MageShaman	0	0	n/a
Warrior	Rogue	1	WarriorRogue	20	MagePriest	0	0	n/a
Shaman	Druid	1	ShamanDruid	21	MageMage	1	1	100.0
Priest	Warlock	0	PriestWarlock	22	MageHunter	0	0	n/a
Mage	Mage	1	MageMage	23	MagePaladin	1	0	0.0
Hunter	Warrior	0	HunterWarrior	24	MageWarlock	1	1	100.0
Paladin	Warrior	1	PaladinWarrior	25	MageRogue	3	2	66.7
Warlock	Rogue	1	WarlockRogue	26	MageWarrior	2	1	50.0
Paladin	Hunter	0	PaladinHunter	27	MageDruid	0	0	n/a
Rogue	Paladin	1	RoguePaladin	28	HunterShaman	0	0	n/a
Warrior	Warlock	0	WarriorWarlock	29	HunterPriest	0	0	n/a
Rogue	Paladin	1	RoguePaladin	30	HunterMage	0	0	n/a
Druid	Rogue	1	DruidRogue	31	HunterHunter	0	0	n/a
Warlock	Warrior	0	WarlockWarrior	32	HunterPaladin	1	0	0.0
Mage	Rogue	1	MageRogue	33	HunterWarlock	0	0	n/a
Warrior	Druid	0	WarriorDruid	34	HunterRogue	0	0	n/a
Rogue	Warlock	1	RogueWarlock	35	HunterWarrior	1	0	0.0
Rogue	Mage	0	RogueMage	36	HunterDruid	0	0	n/a
Mage	Warrior	0	MageWarrior	37	PaladinShaman	0	0	n/a
Mage	Warrior	1	MageWarrior	38	PaladinPriest	0	0	n/a
Paladin	Rogue	0	PaladinRogue	39	PaladinMage	0	0	n/a
Warrior	Rogue	1	WarriorRogue	40	PaladinHunter	1	0	0.0
				41	PaladinPaladin	0	0	n/a
		22		42	PaladinWarlock	0	0	n/a
				43	PaladinRogue	3	1	33.3
				44	PaladinWarrior	2	1	50.0
				45	PaladinDruid	0	0	n/a
				46	WarlockShaman	0	0	n/a
				47	WarlockPriest	0	0	n/a
				48	WarlockMage	0	0	n/a
				49	WarlockHunter	0	0	n/a
				50	WarlockPaladin	0	0	n/a
				51	WarlockWarlock	0	0	n/a
				52	WarlockRogue	1	1	100.0
				53	WarlockWarrior	3	1	33.3
				54	WarlockDruid	0	0	n/a
				55	RogueShaman	0	0	n/a
				56	RoguePriest	0	0	n/a
				57	RogueMage	2	1	50.0
				58	RogueHunter	0	0	n/a
				59	RoguePaladin	2	2	100.0
				60	RogueWarlock	2	2	100.0
				61	RogueRogue	0	0	n/a
				62	RogueWarrior	0	0	n/a
				63	RogueDruid	1	1	100.0
				64	WarriorShaman	0	0	n/a
				65	WarriorPriest	0	0	n/a
				66	WarriorMage	0	0	n/a
				67	WarriorHunter	1	0	0.0
				68	WarriorPaladin	1	1	100.0
				69	WarriorWarlock	2	0	0.0
				70	WarriorRogue	2	2	100.0
				71	WarriorWarrior	0	0	n/a
				72	WarriorDruid	1	0	0.0
				73	DruidShaman	0	0	n/a
				74	DruidPriest	0	0	n/a
				75	DruidMage	0	0	n/a
				76	DruidHunter	0	0	n/a
				77	DruidPaladin	0	0	n/a
				78	DruidWarlock	0	0	n/a
				79	DruidRogue	1	1	100.0
				80	DruidWarrior	1	1	100.0
				81	DruidDruid	0	0	n/a

							battle	percent	rank
							DruidWarrior	100.0	1
							DruidRogue	100.0	2
							WarriorRogue	100.0	3
							WarriorPaladin	100.0	4
							RogueDruid	100.0	5
							RogueWarlock	100.0	6
							RoguePaladin	100.0	7
							WarlockRogue	100.0	8
							MageWarlock	100.0	9
							MageMage	100.0	10
							ShamanDruid	100.0	11
							ShamanHunter	100.0	12
							MageRogue	66.7	13
							RogueMage	50.0	14
							PaladinWarrior	50.0	15
							MageWarrior	50.0	16
							WarlockWarrior	33.3	17
							PaladinRogue	33.3	18
							WarriorDruid	0.0	19
							WarriorWarlock	0.0	20
							WarriorHunter	0.0	21
							PaladinHunter	0.0	22
							HunterWarrior	0.0	23
							HunterPaladin	0.0	24
							MagePaladin	0.0	25
							PriestWarlock	0.0	26
							PriestPaladin	0.0	27

oldbrewer · Nov 23, 2013

would this output satisfy your requirements

markmzz · Nov 23, 2013

If I understand correctly what you want, maybe this can helps:

Layout

TOP 5	Win				Class	Against	Win
Druid	Mage	75%			Druid	Mage	1
Hunter	Hunter	60%			Hunter	Hunter	1
Druid	Druid	50%			Druid	Hunter	1
Druid	Hunter	40%			Mage	Druid	1
Mage	Druid	33%			Mage	Hunter	0
					Druid	Mage	1
	Druid	Hunter	Mage		Druid	Druid	1
Druid	50%	40%	75%		Druid	Mage	1
Hunter	0%	60%	0%		Hunter	Hunter	1
Mage	33%	33%	0%		Druid	Hunter	0
					Mage	Druid	0
					Mage	Hunter	1
					Druid	Mage	1
					Druid	Hunter	0
					Druid	Mage	1
					Hunter	Hunter	1
					Druid	Hunter	0
					Druid	Mage	1
					Hunter	Hunter	0
					Druid	Hunter	1
					Mage	Druid	0
					Mage	Hunter	0
					Druid	Mage	0
					Druid	Druid	0
					Druid	Mage	0
					Hunter	Hunter	0
*******	*******	*******	*******	**	*******	*******	******

Formulas

Code:

In A2

=INDEX($A$9:$A$11,
LARGE(IF(RANK($B$9:$D$11,$B$9:$D$11,1)-ROW($B$9:$D$11)/10^7=LARGE(RANK($B$9:$D$11,$B$9:$D$11,1)-ROW($B$9:$D$11)/10^7,ROWS($A$2:$A2)),
ROW($B$9:$D$11)-ROW($B$9)+1),1))

In B2

=INDEX($B$8:$D$8,
LARGE(IF(RANK($B$9:$D$11,$B$9:$D$11,1)-COLUMN($B$9:$D$11)/10^5=LARGE(RANK($B$9:$D$11,$B$9:$D$11,1)-COLUMN($B$9:$D$11)/10^5,ROWS($B$2:$B2)),
COLUMN($B$9:$D$11)-COLUMN($B$9)+1),1))

In C2

=LARGE($B$9:$D$11,ROWS($C$2:$C2))

In B9

=IFERROR(SUMIFS($H$2:$H$27,$F$2:$F$27,$A9,$G$2:$G$27,B$8)/COUNTIFS($F$2:$F$27,$A9,$G$2:$G$27,B$8),0)

Markmzz

Acallys · Nov 28, 2013

Sorry I've been quite busy and have just managed to sit down and take another look. I tried to use the formulas that you gave me Markmzz, and they seemed to work, but it's coming up with the #VALUE! error in the A2 and B2 brackets and when I drag them down. I currently don't have enough proper statistics to fill out the entire area that has percentages, so I just manually added in some percentages for the sake of it. Here is a copy of the current one with the formulas added in, if you could direct me to why it's giving me the #VALUE! I would be extremely grateful.

Thanks in advance!

https://mega.co.nz/#!2lZE3SRa!RhtsZV1dpfCNc68B1M3gyzIm8noxUPwCriR3W-p7TT0

Acallys · Nov 28, 2013

And thank you Oldbrewer, but I'm not sure what you're doing there. I'm just in need of assistance with the formulas for the cells that Markmzz has given me.

Acallys · Nov 28, 2013

Very sorry, I can't see an edit button. I realized I had missed the formula for B9 so I put that in. This one is more up to date now https://mega.co.nz/#!KtwACRyZ!Vp60czRoN96Is7VEjVfkB2tjbVE2ulXKrK2Izvh2R1M

markmzz · Nov 28, 2013

Acallys said:
Sorry I've been quite busy and have just managed to sit down and take another look. I tried to use the formulas that you gave me Markmzz, and they seemed to work, but it's coming up with the #VALUE! error in the A2 and B2 brackets and when I drag them down. I currently don't have enough proper statistics to fill out the entire area that has percentages, so I just manually added in some percentages for the sake of it. Here is a copy of the current one with the formulas added in, if you could direct me to why it's giving me the #VALUE! I would be extremely grateful.

Thanks in advance!

Hi Acallys,

I'm sorry, my mistake.

Try this:

Code:

In A2 - [COLOR="#FF0000"]use Ctrl+Shift+Enter and not only Enter to enter the formula[/COLOR]

=INDEX($A$9:$A$11,
LARGE(IF(RANK($B$9:$D$11,$B$9:$D$11,1)-ROW($B$9:$D$11)/10^7=LARGE(RANK($B$9:$D$11,$B$9:$D$11,1)-ROW($B$9:$D$11)/10^7,ROWS($A$2:$A2)),
ROW($B$9:$D$11)-ROW($B$9)+1),1))

In B2 - [COLOR="#FF0000"]use Ctrl+Shift+Enter and not only Enter to enter the formula[/COLOR]

=INDEX($B$8:$D$8,
LARGE(IF(RANK($B$9:$D$11,$B$9:$D$11,1)-COLUMN($B$9:$D$11)/10^5=LARGE(RANK($B$9:$D$11,$B$9:$D$11,1)-COLUMN($B$9:$D$11)/10^5,ROWS($B$2:$B2)),
COLUMN($B$9:$D$11)-COLUMN($B$9)+1),1))

In C2 - use only Enter to enter the formula

=LARGE($B$9:$D$11,ROWS($C$2:$C2))

In B9 - use only Enter to enter the formula

=IFERROR(SUMIFS($H$2:$H$27,$F$2:$F$27,$A9,$G$2:$G$27,B$8)/COUNTIFS($F$2:$F$27,$A9,$G$2:$G$27,B$8),0)

Markmzz

Acallys · Nov 28, 2013

And that worked perfectly. Thanks so much for your help!

Acallys · Nov 28, 2013

Actually I'm mistaken, it doesn't seem to be working properly. I'll have to come back and take another look when I get a chance. But what I'm noticing is that currently it's returning me a Shaman, Hunter, 100%, but this is incorrect as that matchup is at a 0 from 1 loss at 0%.

Also where do I put in the digit for the =LARGE($B$34:$J$42,ROWS($C$27:C27)) formula to differentiate between first, second, third, fourth and fifth?

oldbrewer · Nov 28, 2013

=large(A1:A10,1) returns the largest in the range

Help with a lookup formula

Acallys

New Member

oldbrewer

Well-known Member

Excel Facts

oldbrewer

Well-known Member

markmzz

MrExcel MVP

Acallys

New Member

Acallys

New Member

Acallys

New Member

markmzz

MrExcel MVP

Acallys

New Member

Acallys

New Member

oldbrewer

Well-known Member

Similar threads

Forum statistics

Share this page

Help with a lookup formula

New Member

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

New Member

New Member

New Member

MrExcel MVP

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock