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

--
Regards

Roger Govier


"Biff" wrote in message
...
(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,T bl4),2,0)

Copied across.

This is fun!

Biff

"Roger Govier" wrote in message
...
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 InsertNameDefineTbl0 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)

--
Regards

Roger Govier


"Max" wrote in message
...
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 ?

Biff's still requires the 4 tables to be named.
In the original expression .. and you didn't explain what should be
in B1
and what Tbl1, Tbl2, .. mean until the OP asked <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---