# Thread: VLOOKUP confusion (Excel 2000)

1. ## VLOOKUP confusion (Excel 2000)

Hi all,
I need some help using VLOOKUP on the attached workbook sample. I want the yellow column to look for the number in column J, compare it to column D, and return the %inc value associated with it.

I've read the help but it's still not clear to me. I'm a VBA-jock who is really needs to get better acquainted with the formulas <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

Thnx, Deb

2. ## Re: VLOOKUP confusion (Excel 2000)

Deb,

The VLOOKUP function takes the value in the first argument (J6) and looks for the equivalent value in the left most column of the second agrument (\$D\$6:\$G\$18). If successful, the function will return the value from the column indicated by the third argument (2).

=+VLOOKUP(J6,\$D\$6:\$G\$18,2)

IF the optional 4th agrument is either True or omitted, as is the case with your LOOKUP, the first column in the array MUST be in ascending order. A #N/A will be returned if the value in the first arg is less than the lowest value in the lookup table.

If the optional 4th arg is FALSE then the first column of the lookup table doesn't need to be in any order, BUT if an exact match is not found you will get #N/A.

3. ## Re: VLOOKUP confusion (Excel 2000)

Great! Thanks for the work. In the mean time, I had tried Match() and Index() and got the lookup working but I'll go back to VLOOKUP now I understand it.

I really need to go thru J.Walkenbach's Excel Formula book so I too can be a formula-jock (for me, doing VBA is much easier than figuring out which formula to use especially when they're super nested). <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Thnx, Deb <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

4. ## Re: VLOOKUP confusion (Excel 2000)

John is the KING.

Chip Pearson also has a nice web site for things Excel.

5. ## Re: VLOOKUP confusion (Excel 2000)

A word of warning regarding vlookup. I read somewhere (Chip Pearson perhaps) that this a volatile function (or at least it was in versions prior to XP - since then I'm not sure) and that it will be recalculated every time you make a change anywhere in the workbook. This means that if you get carried away and have lots of these looking over large tables, you get lots of time to make coffee etc., If you are looking in the same range and only varying the number of columns offset you are better off using MATCH for the first lookup and then use INDEX based on the MATCH value.

6. ## Re: VLOOKUP confusion (Excel 2000)

Very true, I have also had VLOOKUP cause XL2000 to crash on saving a sheet with 23000 lines. When using VLOOKUP on a big sheet either replace the VLOOKUPs by values only, or turn off automatic calculation.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•