V lookup

Resolved · Low Priority · Version 2016

Thu 2 May 2019, 10:14 Delegate Rachel said...

Rachel has attended:
Excel Intermediate course

V lookup

Why when dragging Vlook up down cells (to copy the format) does it display anything or any errors? I have tried editing the format but it won't link with the data correctly.

Thu 2 May 2019, 13:55 Trainer Jens said...

RE: V lookup

Hi Rachel,

Thank you for the forum question.

If the first cell shows the right result and you get errors (N/A) when you copy it down, then you probably haven't "locked" the table array. The table array must be locked. If the table array is a1:k40 you must have it like this $A$1:$K$40. Please let me know if this is not the issue.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 9 May 2019: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips