another lookup brute force question

[Guest]

is it possible to make a lookup_reference and lookup result directly from a
non-adjacent cells,,

maybe something like this...

=LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&","&L1:M1&","&O11})

like if the value on A1
is exactly equal to a value on either U1 or V1, then RESULT will be from L1
or M1, and if not found, it will search again on the next...

if not possible, please advice

thanks and more power
driller

 

[Bernie Deitrick]

Driller,

Something like

=INDEX(I11,MATCH(A1,U1:AB1,FALSE))

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

4pinoyjunior,

False = 0, and forces an exact match rather than finding the value before the first value that
exceeds the "looked up" number (using a 1) or the converse (using a -1) in a sorted table.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

=IF(NOT(ISERROR(MATCH(A1,U1:V1,FALSE))),INDEX(I1:J1,MATCH(A1,U1:V1,FALSE)),IF(NOT(ISERROR(MATCH(A1,X1:Y1,FALSE))),INDEX(L1:M1,MATCH(A1,X1:Y1,FALSE)),IF(NOT(ISERROR(MATCH(A1,AA1:AB1,FALSE))),INDEX(O11,MATCH(A1,AA1:AB1,FALSE)),"Not
Found")))

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

IF you want to use a simple formula, create links to cells U1:AB1 (with the summing cells W1 and Z1
left out) in another range, say I44, and use the formula

=INDEX(I11,MATCH(A1,I44,FALSE))

HTH,
Bernie
MS Excel MVP