Vlookup Table Array



Kevj said:
Excel can be funny when data is copied between files and sometimes links back to the original file and other times it will accept the relative link.
Click to expand...
The hower's being funny alright.

If I select the relevant sheet/table a certain way it's still bringing up the old name - nee idea how. Any sort of editing brings up the dreading N/A as well instead of a neat little 0. Can't fathom why as nothing's really changed.
 
Hard to tell exactly what you mean and what you are hoping to achieve without seeing it but when I use VLookup I always name the range before writing out the formula. If you don't use absolute references in the formula it can get messy.
 
Heffer said:
Hard to tell exactly what you mean and what you are hoping to achieve without seeing it but when I use VLookup I always name the range before writing out the formula. If you don't use absolute references in the formula it can get messy.
Click to expand...
How would you do that then?



Name manager seems to be me answer. Not even something I've looked at before, yet I put this sheet together years ago. :lol:
 
chriswallace85 said:
The hower's being funny alright.

If I select the relevant sheet/table a certain way it's still bringing up the old name - nee idea how. Any sort of editing brings up the dreading N/A as well instead of a neat little 0. Can't fathom why as nothing's really changed.
Click to expand...

Whether this is the proper way to do it or otherwise, Control + F the replace all text with the new path.
 
Highlight all of the cells you are looking up i.e. the array for the second argument in the formula, then in the top left hand side where the cell location indicator is, (the bit which tells you what cell you are currently in) left click in it and give it a name such as data. Then when you write out the VLookup formula you aren't pissing about with $ symbols. It would be something like =VLookup(A1, Data, 2, false) if you wanted to return the second column value. This can be done across sheets within a workbook.

If you need to edit the range you have just named you go into formula, name manager and the cell range is at the bottom. Shift and F3 is the shortcut (I think, writing this off the top of my head)
 
Last edited:
Heffer said:
Highlight all of the cells you are looking up i.e. the array for the second argument in the formula, then in the top left hand side where the cell location indicator is, (the bit which tells you what cell you are currently in) left click in it and give it a name such as data. Then when you write out the VLookup formula you aren't pissing about with $ symbols. It would be something like =VLookup(A1, Data, 2, false) if you wanted to return the second column value. This can be done across sheets within a workbook.

If you need to edit the range you have just named you go into formula, name manager and the cell range is at the bottom. Shift and F3 is the shortcut (I think, writing this off the top of my head)
Click to expand...

In the workbook where the vlookup works, the formula returns zeros and pound signs when the lookup cell is blank, but in my new workbook, the same formula is returning #n/a. It's driving is nuts. Any ideas?

Google wants me to go down the IF route, but there's no IF formula in my original working workbook. It's just Vlookup.
 
chriswallace85 said:
Why the frig has my formula got a different name for the table array than the worksheet it's looking at (and yet still works fine)???
Click to expand...
Copy and paste the data which you are using as your table array into the same workbook as VLOOKUP as this will be a be less of a risk
Make sure your lookup value is in the correct column too.
 

Back
Top