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
