using a reference table

  • Thread starter Thread starter Heshe
  • Start date Start date
H

Heshe

I'm trying to create a huge reference table lets say of the type

name1 number11 number12 number13
name2 number21 number22 number23

and so on and so forth (lets say there'll be a thousand entries)

and then use it in the following way.

in another table, whenever i write in a cell a certain name lets say
nameX... i want to program the neighbouring cells to retrieve the
information from the reference table about the nameX and to write then
in automatically
i.e. i write
nameX ____ _____ _____ and in the blanks right away it finds the
numbers from the reference table and fills them to result in:

nameX numberX1 numberX2 numberX3.


How can I possibly do that?
 
one way:

Assume your table is in sheet1, columns A:D. Also assume that you
enter the name in Sheet2, A2, and want the results to appear in
B2:D2. Then:

B2: =IF($A2<>"",VLOOKUP($A2,Sheet1!$A:$D,2,FALSE),"")
C2: =IF($A2<>"",VLOOKUP($A2,Sheet1!$A:$D,3,FALSE),"")
D2: =IF($A2<>"",VLOOKUP($A2,Sheet1!$A:$D,4,FALSE),"")
 
You can use a worksheet formula:

Say you typed the NameX in A2,
then in B2:
=vlookup($a2,sheet2!$a:$e,2,false)

Assumes you want the second column brought back (the final 2 in the formula).

You could enhance the formula to hide errors:

=if($a2="","",if(iserror(vlookup($a2,sheet2!$a:$e,2,false)),"Missing",
vlookup($a2,sheet2!$a:$e,2,false))

(all one cell.)

You can replace Missing with whatever you like--including "".
 
Back
Top