Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Working TOO Well
Here is a lookup formula I made for the user to enter a unique four digit #
into a spreadsheet (D2) and have correlating data returned (don't worry, I made a preliminary sort macro for the data list "VOUCH"): =IF(D20,LOOKUP($D2,VOUCH!D$2:D$5000,VOUCH!C$2:C$5 000),"") The problem is that if and when the user enters a # incorrectly or a # that is not on the list....Excel automatically looks up the closest match on the data list and returns THAT-WRONG correlating data. Is there a way to tell Excel to return a message to the user that the # is not existent / incorrect? Or is there a way to limit the lookup return to specific #'s ONLY on the "VOUCH" list? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Working TOO Well
Hi,
I assume that you pull your data from Vouch! Column D and your four digit # is in column C in E2 enter =sumproduct(--(D2=Vouch!$c$2:$c$5000),vouch!$d$2:$d$5000) "Teddy-B" wrote: Here is a lookup formula I made for the user to enter a unique four digit # into a spreadsheet (D2) and have correlating data returned (don't worry, I made a preliminary sort macro for the data list "VOUCH"): =IF(D20,LOOKUP($D2,VOUCH!D$2:D$5000,VOUCH!C$2:C$5 000),"") The problem is that if and when the user enters a # incorrectly or a # that is not on the list....Excel automatically looks up the closest match on the data list and returns THAT-WRONG correlating data. Is there a way to tell Excel to return a message to the user that the # is not existent / incorrect? Or is there a way to limit the lookup return to specific #'s ONLY on the "VOUCH" list? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup between two worksheets not working well | Excel Worksheet Functions | |||
Lookup working days | Excel Worksheet Functions | |||
lookup not working | Excel Worksheet Functions | |||
LOOKUP is working - but VLOOKUP is not | Excel Discussion (Misc queries) | |||
LOOKUP formula not working right | Excel Worksheet Functions |