Multiple VLOOKUP

[SteveH]

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

 

[jlclyde]

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,vlookup(a2,look,
2,false),if(iserror(vlookup(a2,look2,2,false)=False,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

 

[SteveH]

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

 

[T. Valko]

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,IF(COUNTIF(Look2,A2),Look2,Look3)),2,0))

 

[Max]

.. 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
---

 

[Max]

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
---

 

[SteveH]

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

Steve

 

[T. Valko]

You're welcome. Thanks for the feedback!