lookup over multiple ranges

T

thedevilkaiser

I want something to the effect:
=if(iserror(vlookup(a1,range1,2,false)),vlookup(a1,range2,2,false),vlookup(a1,range1,2,false))

but I want to do this without using an if iserror function. Is there a
way to do this? An array formula perhaps?

Thanks.
 
B

bobocat

it is possible, if both Range1 and Range2 contain same number of rows

Step1
Define range name
Range1 = $e$5:$f$10
Range2 = $k$5:$l$10
..
..
..
Lookup Value placed in H5
=vlookup(h5, indirect("Range"&sumproduct((e5:e10=h5)*1+(k5:k10=h5)*2)),2)
 
Top