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

#### Johnpow

##### New Member
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?

### 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
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
I have been reading where Arrays require specific ascending order and AlanY, you come up with the solution.

Thank you for speedy assistance.

you're welcome

#### Johnpow

##### New Member

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
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

#### Johnpow

##### New Member

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
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
Thanks AlanY.
Will 'stick with the knitting' so I don't complicate the issue.

Replies
4
Views
154
Replies
3
Views
334
Replies
8
Views
36
Replies
5
Views
60
Replies
3
Views
53