Hi

I am using the following statement to VLOOKUP a part from name range "look"

=IF(ISBLANK(A2),"",(VLOOKUP(A2,look,2,FALSE)))

I need to be able to lookup multiple ranges i.e. look2, look3

How do I modify the above statement to successfully look for "part A" in
look, look2 and look3?

Cheers

And what happens if the result is found in more than one range?


--
Pecoflyer

Cheers - MS Excel Newbie
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=43356

On Dec 22, 7:55*am, SteveH wrote:
Hi

I am using the following statement to VLOOKUP a part from name range "look"

=IF(ISBLANK(A2),"",(VLOOKUP(A2,look,2,FALSE)))

I need to be able to lookup multiple ranges i.e. look2, look3

How do I modify the above statement to successfully look for "part A" in
look, look2 and look3?

Cheers

You will need to name soem more ranges and use iserror. For
instance: =IF(Iserror(vlookup(a2,look,2,false))=false,vlooku p(a2,look,
2,false),if(iserror(vlookup(a2,look2,2,false)=Fals e,vlookup
(a2,look2,2,false)))

Soemthign like that. I knwo there is an easier way, but it is not
coming to me right now.
Jay

Hi there, it won't be its a part list with each item appearing once

"Pecoflyer" wrote:


And what happens if the result is found in more than one range?


--
Pecoflyer

Cheers - MS Excel Newbie
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=43356

One way, assuming the lookup_value will *only* be in the first column of the
lookup_table.

=IF(A2="","",VLOOKUP(A2,IF(COUNTIF(Look,A2),Look,I F(COUNTIF(Look2,A2),Look2,Look3)),2,0))

--
Biff
Microsoft Excel MVP


"SteveH" wrote in message
...
Hi there, it won't be its a part list with each item appearing once

"Pecoflyer" wrote:


And what happens if the result is found in more than one range?


--
Pecoflyer

Cheers - MS Excel Newbie
------------------------------------------------------------------------
Pecoflyer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=43356

.. it won't be, its a part list with each item appearing once

Nonetheless ... I'd set it up in this simple manner
so that it's v.clear exactly what each vlookup is returning

With defined lookup ranges listed in B2 across,
eg: lookup, lookup2, etc
(ensure these listings match exactly with the defined names,
except for case)

Place in B2:
=IF($A2="","",(VLOOKUP($A2,INDIRECT(B$1),2,0)))
Copy B2 across/fill down to populate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

To complete the earlier set up ..
If the defined ranges are listed in say B1:Z1
To return the 1st non-error value (from left to right across cols B to Z)
place this in AA2, normal ENTER:
=INDEX(B2:Z2,MATCH(TRUE,INDEX(NOT(ISERROR(B2:Z2)), ),0))
Copy AA2 down. Col AA will return the desired results of the "multiple"
lookup.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

You are a star!!! thanks very much I am sorted now!

Steve

"T. Valko" wrote:

One way, assuming the lookup_value will *only* be in the first column of the
lookup_table.

=IF(A2="","",VLOOKUP(A2,IF(COUNTIF(Look,A2),Look,I F(COUNTIF(Look2,A2),Look2,Look3)),2,0))

--
Biff
Microsoft Excel MVP


"SteveH" wrote in message
...
Hi there, it won't be its a part list with each item appearing once

"Pecoflyer" wrote:


And what happens if the result is found in more than one range?


--
Pecoflyer

Cheers - MS Excel Newbie
------------------------------------------------------------------------
Pecoflyer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=43356

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"SteveH" wrote in message
...
You are a star!!! thanks very much I am sorted now!

Steve

"T. Valko" wrote:

One way, assuming the lookup_value will *only* be in the first column of
the
lookup_table.

=IF(A2="","",VLOOKUP(A2,IF(COUNTIF(Look,A2),Look,I F(COUNTIF(Look2,A2),Look2,Look3)),2,0))

--
Biff
Microsoft Excel MVP


"SteveH" wrote in message
...
Hi there, it won't be its a part list with each item appearing once

"Pecoflyer" wrote:


And what happens if the result is found in more than one range?


--
Pecoflyer

Cheers - MS Excel Newbie
------------------------------------------------------------------------
Pecoflyer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=43356