MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fill in the blanks

Posted by Julie S. on August 13, 2001 9:04 AM

When I type the code "JLS", I want Exel to fill in my name
phone number, and FAX number in it's own cell.

Posted by Aladin Akyurek on August 13, 2001 9:09 AM


Where can Excel find that info?


Posted by Julie on August 13, 2001 9:33 AM

In a small table on the same spreadsheet or on it's
own spreadsheet - which ever is easier!

Posted by Aladin Akyurek on August 13, 2001 10:20 AM


The first column of your table should contain the codes, e.g.,

JLS,Julie,phone-number,fax-number, and so on ("," indicates defferent columns).

Lets say that this table is in the same workbook in a worksheet of its own. Select all of the cells (excluding the column headings like Code, First Name, Last Name, etc), go to the Name Box, and type, e.g., DTABLE. Select all of the column headings and name it (again via the Name Box) FIELDS. Also select all of the codes (excluding the column headings Codes) and name it CODES.

Lets assume that you want to enter a code in column A from A2 on in a different worksheet.

In B2 enter: =IF(ISNUMBER(MATCH(A2,CODES,0)),VLOOKUP(A2,DTABLE,MATCH("Last Name",FIELDS,0),0),"Error: Code Not Found.")

In C2 enter: =IF(LEN(B2)>0,VLOOKUP(A2,DTABLE,MATCH("Phone Number",FIELDS,0),0),"")