Displaying results of Vlookup

[chrisandsally]

Hi,

I have attached the workbook. Upon a 6 digit being entered into a textbox it looks within the number range and displays the corresponding result, Answer 1,2 and 3.

What am I doing wrong?

Any help really appreciated

lookup.xlsm

[Glenn Kennedy]

I assume that the entry in D8 is a mistake. a) it is lower than C8 and b) it is lower than your lookup value. Its value does not make any difference to the lookup itself; however. Was this what you were expecting to see?

[StuCram]

The parameters to your VLOOKUP are incorrect.
ex Textbox4 probably has the value to search for in Vlookup.
The range for the lookup table should be C5:H8.
The results will be found in columns 4, 5 and 6 of the range.
Check Excel's Help file for details on VLOOKUP.

Also, you are attempting to store all 3 results in one text box.

Try this..

Please Login or Register  to view this content.

[chrisandsally]

Hi Glenn

Yes D8 was a mistake. I have a list of ranges in C&D and if it falls within the range it outputs the three different answers. I guess a better example would be a value of 125000 should reveal answers C D E.

Thank you

[Glenn Kennedy]

I guess a better example would be a value of 125000 should reveal answers C D E.

...which is what the formula in the sheet I posted above does return.

[chrisandsally]

Yes, you are right. In the file you sent above the code requires a password, how can i see the code? Or are you still looking at it?

[Glenn Kennedy]

Ooops. I just noticed that this is in the VBA forum. I just used a regular formula. If it's VBA you're after - I'm out... I'm no use with VBA!

[chrisandsally]

No Problem, thanks anyway

[chrisandsally]

StuCram

Thank you for your help on this. I have attached a revised Workbook however still as some errors. Please could you take another look?

lookup2.xlsm

[StuCram]

I've modified your 'Lookup2' sample file and attached the newer version.

Summary of Changes:
- added a 'Close' button
- added a button and code to show the user form
- Defined a range in the lookup routine for the data area
---- this simplifies the vLookup formulas and makes changes to the range easier
- Created and used a numeric variable (type Long)
---- This also simplifies the vLookup formulas and ensures the correct data type
- Modified the data table to check for values that are too small or too big (Shown in grey but likely hidden)

Hope this helps

lookup2.xlsm

[chrisandsally]

Thank you very much, a star.