lookup over multiple ranges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thedevilkaiser@gmail.com - 21 Nov 2006 01:27 GMT

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.

Reply to this Message

bobocat - 21 Nov 2006 04:09 GMT

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)

<thedevilkaiser@gmail.com>
???????:1164072471.121573.61230@m73g2000cwd.googlegroups.com...

>I want something to the effect:
> =if(iserror(vlookup(a1,range1,2,false)),vlookup(a1,range2,2,false),vlookup(a1,range1,2,false))
[quoted text clipped - 3 lines]
>
> Thanks.

Reply to this Message