Look Up Function

R

rojobrown

I have a table set up so that a user can key in one number and it flood in
the rest of her information based on a table that I have set up on another
worksheet. The formula works fine if the exact number is keyed in. However,
if she keys in the wrong number, it still pulls information. How can I make
it pull over only if the data is exact?
This is the formula that I have:

=LOOKUP('Data Entered In'!A2,'Data Table'!$A$2:$A$49,'Data Table'!$A$2:$A$49)
 
T

Trevor Shuttleworth

Maybe you could use VLOOKUP instead. Something like:

=IF(ISNA(VLOOKUP(A1,$A$2:$A$49,1,FALSE)),"Entry not
found",VLOOKUP(A1,$A$2:$A$49,1,FALSE))

Your example would look like this (I think):

=IF(ISNA(VLOOKUP('Data Entered In'!A2,'Data
Table'!$A$2:$A$49,1,FALSE)),"Entry not found",VLOOKUP('Data Entered
In'!A2,'Data Table'!$A$2:$A$49,1,FALSE))

Regards

Trevor
 
D

daddylonglegs

LOOKUP works on a sorted lookup range and will return the greatest value
smaller than the lookup value if there is no exact match.

The formula you quote doesn't seem correct, presumably the result
vector should be a column other than A.

Assuming your result vector is column B and column A is sorted in
ascending order you can use

=IF(LOOKUP('Data Entered In'!A2,'Data Table'!$A$2:$A$49)='Data Entered
In'!A2,LOOKUP('Data Entered In'!A2,'Data Table'!$A$2:$B$49),"No Match")
 
R

rojobrown

Thanks.... It worked like a charm....
--
Thanks a bunch!
rojobrown


Trevor Shuttleworth said:
Maybe you could use VLOOKUP instead. Something like:

=IF(ISNA(VLOOKUP(A1,$A$2:$A$49,1,FALSE)),"Entry not
found",VLOOKUP(A1,$A$2:$A$49,1,FALSE))

Your example would look like this (I think):

=IF(ISNA(VLOOKUP('Data Entered In'!A2,'Data
Table'!$A$2:$A$49,1,FALSE)),"Entry not found",VLOOKUP('Data Entered
In'!A2,'Data Table'!$A$2:$A$49,1,FALSE))

Regards

Trevor
Click to expand...
 
Top