Formula using OFFSET and LOOKUP in an array not calculating correctly.

Johnpow

New Member
Joined
Feb 19, 2017
Messages
18
Hello all,

Working on a Footy Performance selection EXCEL 2010 matrix to enable experienced assessors to record player performance and then select a team of performing players.

The formula I am trying to use is not working correctly but I cannot find the error.

'=IFERROR(IF(G20>=OFFSET(K20,,LOOKUP(E20,{"F1","F2","T1","T2","E1","E2","E3","M"},{0,1,2,3,4,5,6,7})),$A$22,IF(G20>=$J$21*OFFSET(K20,,LOOKUP(E20,{"F1","F2","T1","T2","E1","E2","E3","M"},{0,1,2,3,4,5,6,7})),$A$21,$A$20)),"")

Seems Lookup will not look up more than 4 columns to the right, where I need to look at 8 columns. Columns 5,6,7,8, present a blank cell.
Hope someone can assist with corrections to the formula please.
Maybe should be using different functions?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
the lookup() function works with the array in order, try change your lookup array from

{"F1","F2","T1","T2","E1","E2","E3","M"},{0,1,2,3,4,5,6,7}

to

{E1 E2 E3 F1 F2 M T1 T2} and {4 5 6 0 1 7 2 3}
 

Johnpow

New Member
Joined
Feb 19, 2017
Messages
18
I have been reading where Arrays require specific ascending order and AlanY, you come up with the solution.

Thank you for speedy assistance.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Johnpow

New Member
Joined
Feb 19, 2017
Messages
18

ADVERTISEMENT

Further to the above solution, is there a non-Array based and non -VBA. formula for the same data which does not require the cells to be in specific order please?
Maybe using different functions?
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you can use a vlookup() instead

Code:
=VLOOKUP(D1,{"F1",0;"F2",1;"T1",2;"T2",3;"E1",4;"E2",5;"E3",6;"M",7},2,0)

or with a table

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">F1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">E2</td><td style="text-align: right;background-color: #E2EFDA;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">F2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">T1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">T2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">E1</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">E2</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">E3</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">M</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D1,$A$1:$B$8,2,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

Johnpow

New Member
Joined
Feb 19, 2017
Messages
18

ADVERTISEMENT

Thank you again AllanY.

That still contains and Array within the Vlookup?
Will INDEX MATCH also work? so I don't have to worry about which direction the lookup column is? - or maybe I am over-thinking this?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
index/match will also contain array, unless you can use a nested if(), like this

=if(A1="F1",0,if(A1="F2",1,if(etc. etc...)))))

can't really see the benefit though
 

Johnpow

New Member
Joined
Feb 19, 2017
Messages
18
Thanks AlanY.
Will 'stick with the knitting' so I don't complicate the issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,027
Messages
5,526,329
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top