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

[Johnpow]

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?

 

[AlanY]

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]

I have been reading where Arrays require specific ascending order and AlanY, you come up with the solution.

Thank you for speedy assistance.

 

[AlanY]

you're welcome

 

[Johnpow]

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?

 

[AlanY]

you can use a vlookup() instead

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

or with a table

Book1
	A	B	C	D	E
1	F1	0		E2	5
2	F2	1
3	T1	2
4	T2	3
5	E1	4
6	E2	5
7	E3	6
8	M	7
Sheet1

Cell Formulas
Range	Formula
E1	=VLOOKUP(D1,$A$1:$B$8,2,0)

 

[Johnpow]

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]

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]

Thanks AlanY.
Will 'stick with the knitting' so I don't complicate the issue.