Hi All,



Novice here.

Looking for support with a sheet I'm trying to build related to a game. The issue is dividing a Hlookup by a Vlookup. It works in a single sell but when I use an array to fill for multiple columns it pulls in the same answer for 4 cells and then the second answer for four cells. Every cell should be different, formulas and my thinking below.

two tables:
The first, includes horizontal data of player levels listed 1-99, left to right,
The second row, under this, is the experience required to complete that level. e.g. 500 1,324 2,560 4,208 6,268 8,740 11,624 14,920 18,628 22,748 for the first 10 levels.

The Second table: Has a vertical list of all craft able recipes in the game in the first column: e.g. Wood Boards,
The second column has the experience generated at level 1: 720.00,

This table then runs left to right with each level, as the recipes experience provided reduces from 100%, when you gain the recipe at the specific level, to 75% after +4 player levels, 50% at +8 player levels etc. e.g. 720.00 720.00 720.00 720.00 540.00 540.00 540.00 540.00 360.00 360.00

I am trying to build a summary table, using Hlookup to pull the total experience needed for a level /divided by Vlookup to pull the experience given by that recipe at that level.

My formula =HLOOKUP(N217,$N$3:$DH$5,2,FALSE)/VLOOKUP(C219,$C$9:$DH$214,12,FALSE) works for a single cell. But I want to copy this across for levels 1-99 and down for every recipe.


HLOOKUP:
N217 = level 1-99 in summary table
$N$3:$DH$5 = Table of levels and experience required
2 = The experience for the level

VLOOKUP:
C218 = name of recipe
$C$9:$DH$214 = Table of recipes and decreasing experience for each level
12 = Experience given for level 1 for all recipes


When I add an array, as below, the same answer is generated for the first four levels and then the next four levels e.g. 0.7 0.7 0.7 0.7 0.9 0.9 0.9 0.9 1.4 1.4

Instead of increasing in the number as the level experience required increased and experience gains from recipe decreases. It should look like. 0.7 1.8 3.6 5.8 11.6 16.2 21.5 27.6 51.7 63.2

Can someone flag what is wrong with the below causing this initial repetition?

=ArrayFormula(HLOOKUP(N217,$N$3:$DH$5,3,FALSE)/VLOOKUP(C219,$C$9:$DH$214,{12,13,14,15,16,17,18,19 ,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,3 6,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52, 53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69 ,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,8 6,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,1 02,103,104,105,106,107,108,109,110},FALSE))

crossposted: Hlookup divided by Vlookup (Game levelling optimisation) [Google Sheets]

See if cells N221 and N223:W223 might be in the right direction.
Otherwise attach a workbook with this and expected results, because it's difficult to guess correctly your setup.

Attached Files

msOffieForums51499.xlsx (67.5 KB, 0 views)

If this is a Googlesheet question you should indicate it in the thread title, so that members don't have to read your post first...

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post