MrExcel Publishing
Your One Stop for Excel Tips & Solutions

number input returns name from list


Posted by Tony Brocksmith on May 02, 2001 3:40 PM

Is there a method whereby I can input a 4-digit code which would return the corresponding name associated with that 4 digit code. The list would be two vertical columns, the first requiring input of the 4-digit code. The column to its right would return the associated name. I need to be able to sort/analyze the list when done. Thanks for any help or guidance.


Posted by Aladin Akyurek on May 02, 2001 4:14 PM

Tony

You need also a table of 4-digit numbers and the associated names somewhere, say, on some other sheet.

Lets say that you have a large table, probably also containing other data (e.g., address, etc.) in the range A2:E500 on Sheet2.

On sheet1, you want to retrieve names, say, in column in B from B2 on by entering 4-digit codes in A from A2 on.

Enter in B2 the following formula:

=IF(ISBLANK(A2),"",IF(ISNUMBER(MATCH(A2,Sheet2!$A$2:$A$500,0)),VLOOKUP(A2,Sheet2!$A$2:$E$500,2,0),""))

Copy down this formula as far as you need.

0 in MATCH and VLOOKUP means FALSE, which is a flag to enforce an exact match.

Aladin

=================

Posted by Dave Hawley on May 02, 2001 11:37 PM

Hi Tony

If I have go you right, you use this. the example assumes your four digit codes are in Column A and the names are in Column B

Select cell D1 Or any cell.
Go to Data>Validation
Select "List" from "Allow" box
In the "source" box type =A1:A500
Click OK

Now select D1 and pick your four digit code from the Validation list.

In any cell type this formula:
=VLOOKUP(D1,A:B,2,FALSE)

Dave


OzGrid Business Applications