Vlookuo

[jilljhop]

Hi sorry for the question but I have been unable to find a clear answer.....


Is it ok to use a partial selection of a spreadsheet for a vlookup for example the spreadsheet is 20 columns long the data your matching against is column 5 and the info you want to pull is column 11 is it ok to select column 5 to 11 and put the argument in your looking at column 7 or should you always select the whole table?

Also I have read this I put a 0 instead of false but they are the same what is the best practice or does it really not matter?

Thank you

 

[AhoyNC]

Yes you can use a partial selection. You can use 0 for FALSE or 1 for TRUE. You may want to look at the XLOOKUP function since you have Excel 2021 which is more versatile than VLOOKUP.

 

[jilljhop]

Hi that's great thank you I have looked at xlookup in the past but have not really used it but I will have another look.

Is it best practice to use the whole table..
If you have the look up info on column 5 would that always need to be moved to column A? As it's the most left?

I also copy and paste the data once it's moved over so it doesn't change if the original table does is this also ok?

I just want to ensure I'm using it in the best way possible.

Thank you

 

[AhoyNC]

With VLOOKUP your lookup value has to be to the left most column in your table array, but doesn't need to be in column A. I don't know of any reason to use the whole table if the other columns aren't needed.
You may want to copy and use paste as value to make sure your data doesn't change.
Maybe this link will help.

 

[Alex Blakenburg]

You might be interested in @Peter_SSs post below. It seems that including columns you don't need may slow the spreadsheet performance down because the formula is then subject to recalcs when a cell within that range changes.
How can I write a formula to add an amount to a VLookup rule based on a number or text string in a different cell?