VlookUP Multiple tables

[joeldsmt]

Hello,

I dont have a clue what to do and any assistance would be greatl
appreciated. Here is what i want to do:

I presently have a worksheet that has for columns. Each column has
look up table that it searches when i enter a specific number in colum
A. So lets say i enter 2000 in column A.Column B will go to my firs
look up table and get me a number. Columns C will go to my secon
table, D will look at my third table, and E will look up my Fourt
table. This process is working just. The problem is this... I no
have more than 4 tables and only 4 columns to work with.

Is there a way that I can enter a Number on the top of any of thes
columns and then the proper lookup table will be searched. Fo
instance if i enter #1 on the top of column B it will search my firs
table, but if i enter #2 instead it will search my second table.

Any ideas?

Thank you all in advance.

J

 

[Max]

One way via INDIRECT & named table_arrays

Name* the lookup table_arrays as say:
Table1, Table2, Table3, Table4, etc
*via Insert > Name > Define, or via the namebox

Then we could use these names as labels in B1:E1
and deploy something like this in B2:
=VLOOKUP($A2,INDIRECT(B$1),2,0)
to return from Table1 in col B

B2 could then simply be copied across and filled down to return
correspondingly from the names entered in C1:E1, viz from: Table2, Table3,
Table4 in cols C to E

 

[Biff]

A non-volatile alternative:

=VLOOKUP(A2,CHOOSE(B1,Tbl1,Tbl2,Tbl3,Tbl4),2,0)

Biff

 

[joeldsmt]

Biff,

Although i dont understand either of your formulas, your answer seems
bit easier.

Could you explain to me what each section is?

=VLOOKUP(A2,CHOOSE(B1,Tbl1,Tbl2,Tbl3,Tbl4),2,0)

I understand the first half but i get confused on the CHOOSE part. Ho
does it know what table i want to look from? What does the 2,0 par
do?

Thank you in advance

 

[Biff]

Biff,

Although i dont understand either of your formulas, your answer seems a
bit easier.

Could you explain to me what each section is?

=VLOOKUP(A2,CHOOSE(B1,Tbl1,Tbl2,Tbl3,Tbl4),2,0)

I understand the first half but i get confused on the CHOOSE part. How
does it know what table i want to look from? What does the 2,0 part
do?

Thank you in advance.

Ok.....

Based on your post.......

Assume you have 4 separate lookup tables. You have given each table a
defined name: Tbl1, Tbl2, Tbl3 Tbl4.

In B1 you enter the number of the lookup table that you want to use, either
1 for Tbl1, 2 for Tbl2, 3 for Tbl3, or 4 for Tbl4.

The CHOOSE function takes that number and selects the appropriate table to
use for the Vlookup. You'll notice that in the formula the tables are listed
in sequence. Let's say you enter 2 in B1. CHOOSE will select the 2nd entry
in the sequence which happens to be Tbl2. If you enter 4 in B1 then CHOOSE
will select the 4th entry in the sequence which is Tbl4.

The 2,0..........

2 is the column number of the lookup table from which the result will be
taken. That's just an arbitrary number I used as an example.

0 means that you're wanting an exact match of the lookup value.

Biff

 

[Max]

Biff, .. your answer seems a bit easier.

Not really <g>. Mine is actually shorter, and I've configured it in a way
which enables you to easily copy across and fill down. It's a good intro to
the use of INDIRECT, and how you can use it to read col headers (text) which
are directly synonymous with the 4 tables' names as created. So you know
what's going on, and you could probably experiment successfully with its use
in other situations (cross-apply elsewhere). Note that, like mine, Biff's
still requires the 4 tables to be named. Yes, INDIRECT is volatile, but it
has many flexible uses, and I don't think it's volatility should preclude its
use only as a last resort. Of course it's your choice to use either.

 

[Biff]

Mine is actually shorter

Biff's still requires the 4 tables to be named.

=VLOOKUP(A2,CHOOSE(B1,D1:E5,F1:G5,H1:I5,J1:K5),2,0)

Biff

 

[Max]

Mine is actually shorter
was prompted by OP's statement ...

.. your answer seems a bit easier.

... and then the apparent contradiction sunk in as to why the seemingly
"easier" answer required clarification to understand how it worked ?
In the original expression .. and you didn't explain what should be in B1
and what Tbl1, Tbl2, .. mean until the OP asked <g>

 

[Roger Govier]

Hi

As another alternative, you could take the Choose() function out of
Biff's formula and into a defined name
Assuming you have the 4 named ranges Tbl1 to Tbl4
Define a 5th name Insert>Name>Define>Tbl0 Refers to
=CHOOSE($B$1,Tbl1,Tbl2,Tbl3,Tbl4)
Use B1 to define which table you want, 1,2,3 or 4

Then use
=VLOOKUP(A2,Tbl0,2,0)

That way the Choose function gets written once, as opposed to being
written across a whole range of cells.
(and I won't even say its shorter, Max<vbg>)

 

[Biff]

(and I won't even say its shorter, Max<vbg>)

LOL!

And, if you wanted a return from each table (that might mean you don't use
B1 as table variable)

=VLOOKUP($A2,CHOOSE(COLUMNS($A:A),Tbl1,Tbl2,Tbl3,Tbl4),2,0)

Copied across.

This is fun!

Biff

 

[Roger Govier]

This is fun!
Isn't it!!!

Very good idea Biff, but you could still put the CHOOSE part in defined
name of Tbl0
Define Tbl0 =CHOOSE(COLUMNS($A:A),Tbl1,Tbl2,Tbl3,Tbl4)

=VLOOKUP($A2,Tbl0,2,0)

Would make it a bit more readable when inserted inside an error trap

=IF(ISERROR(VLOOKUP($A2,Tbl0,2,0)),"",VLOOKUP($A2,Tbl0,2,0))

 

[Max]

Isn't it!!!

But of course !!! <g>

However ... as-is, CHOOSE's index_num limits it's usage/the formula's
propagation to cover between 1-29 tables, while with INDIRECT it's just a
sweet fill across of the formula in B2 up to the last IV2 to reference a
possible 255 tables (assuming OP has this need <g>). That's almost 9 times
more coverage afforded with the same "short" original formula ( ... imagine
how long the CHOOSE formula is going to look like with 29 tables slotted in
??). And unlike using CHOOSE here, which hardcodes the tables' order within
the formula, OP doesn't need to edit and refill the formulas all over should
there be a future change needed in the col referencing order of the max 255
tables. He just need to change the col labels accordingly / easily.

Long live INDIRECT !!! <bg>

 

[Biff]

Long live INDIRECT !!! <bg>

Try using INDIRECT with dynamic tables!

It's CHOOSE to the rescue!

Biff

 

[Max]

Notice how "volatile" this thread has become ? That's the spirit <g>
(It's pretty quiet elsewhere, especially wrt feedback from OPs to responders
... just rough-count how many posts where OPs respond to responders ..)

Anyway ... up, up, up with ALL volatile functions !! <bg>
(just a clarion call not to shy away from using these, that is)

 

[Biff]

I need to learn me some VBA. I know one line of code:

Application.Volatile

Biff

 

[Ragdyer]

Believe me Max ... you wouldn't be saying that if you were responsible for
keeping a plant going which depended on using several 30 to 40 K row WBs,
which also have in excess of 150 columns.

Of course, they didn't start out that big, but after being exposed to the
problems (calc & re-calc & opening times) involved with *large* files, one
automatically becomes a miser where freely spending "resources" is
concerned.
Even using the "wrong" type of error checking (dbl and triple vlookups)
becomes very "expensive".

Volatiles just add to the nails in the coffin.<g>

I'm *very* gun-shy when it comes to using them.
And, of course, I'm *not* talking about a 2 sheet, 100 row production
report.

 

[Max]

Believe me Max ... you wouldn't be saying that ..

RD, don't ride me so hard on that <g>
It's tongue-in-cheek, and I did throw in a qualifier line below ..

 

[RagDyeR]

I know Max, and I did add a <g> somewhere near the end ... didn't I?<bg>

I must tell you and Biff, that this thread was a throw-back to the "old"
days of these groups, and gave me a pleasant, nostalgic feeling.

When I first "tripped" over these groups, back in '97, I was trying to learn
XL so that I could put together something to run a department with.

Let me tell you, that in almost every post from the "regulars", there was
this 'under-the-surface' *intent*, to be the *first* with an answer, or to
post the*shortest* formula.
And of course, the best was to find an error in someone else's formula *and*
come back with your own that was half the length.
All done with a good, mutual feeling of respect for each other.

You cannot get this "friendly confrontational" atmosphere from reading the
old posts on Google, because there, they're grouped into subjects.
You'd have to go down a group, post after post, thread after thread, to see
how one after the other, the group would reflect this friendly
one-ups-manship.

Can you picture an entire group of Harlans?
Well ... maybe not really *that* bad!<bg> (with due fond respect Harlan)<g>

And believe me, this *did* present a very good "learning" experience.

There's been some posts the past few days on this subject of "is shorter
better?".

Shorter is always better, because, to present something shorter, you
*automatically* submit *another* avenue of thought to add to the first,
therefore introducing more knowledge to the entire group and the archives.

I'm rambling ... <g>

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Believe me Max ... you wouldn't be saying that ..

RD, don't ride me so hard on that <g>
It's tongue-in-cheek, and I did throw in a qualifier line below ..

 

[Biff]

Can you picture an entire group of Harlans?

OMG!

Biff

 

[Ragdyer]

I don't know if it's age (MINE or his), but lately, I seem to sense a
mellowing of demeanor in his posts.<g>

I'm talking about when he's *unprovoked*!

With threads such as Aaron's, he's as tough as ever.<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Can you picture an entire group of Harlans?

OMG!

Biff