Vlookup

[dinokath]

Hi all,

Trying to get this formula correct but I keep getting #Value error.

I have a unique identifier for each line of data and it is contained in cell A2 on sheet #1. I need column K on sheet #1 to look at the unique identifier on sheet #1 in cell A2 (and on down to cell A100+) and reference sheet #2, with the unique identifier in sheet #2 also being in cell A2 (and down to cell A100+) and pull in the value on sheet #2, column I.

This is the formula I have entered on sheet #1, cell K2 (and down to K100+) and clearly I am all messed up.

=VLOOKUP(A2,'Export Last Week Committed'!$A$2, 'Export Last Week Committed'!$I$2:$I$150)

Any help is much appreciated!

 

[Joe4]

Yes, your formula is all messed up. The second argument is the range of your lookup range, not the third one.
See here for instructions on using VLOOKUP.

 

[dinokath]

Where here?

 

[Joe4]

Sorry, just a simple Google search on VLOOKUP brings up the Microsoft documentation on the function.
(Note that you can do this for all Excel functions).

VLOOKUP function - Microsoft Support

Learn how to use function VLOOKUP in Excel to find data in a table or range by row. Our step-by-step guide makes vlookup in excel easy and efficient.

support.microsoft.com

 

[dinokath]

Yeah, that's why I am here. I Googled it, wrote it the way I understood it from the Google search, it didn't work, tried it several different ways, couldn't even get it to give me an error result and now I am here....

 

[Joe4]

Look at the breakdown of the 4 arguments in the VLOOKUP function:

Your description is not all that clear, but I am thinking maybe you want your formula to look something like this:
=VLOOKUP(A2,'Export Last Week Committed'!$A$2, 'Export Last Week Committed'!$I$2:$I$150)

Argument 1 - The value you want to look up:
A2
You have this part fine, this is the value you are trying to look up

Argument 2 - The range you want to look up into, where the left-most column is the column you are matching on:
'Export Last Week Committed'!$A$2
This is incorrect, you only have a single cell here, and not a multi-row range (that could also have multiple columns)

Argument 3 - The column index of the range you want the return value to come from:
'Export Last Week Committed'!$I$2:$I$150
This is incorrect. This argument should always be a number. If you are looking up into a one column range, the value here would be 1.
If, let's say you were looking up into the range I2:M150, matching on column I, but wanting to return the value from column M, it would be 5, as column M is the 5th range when looking at columns 1:5

Argument 4 - This is a boolean value to indicate whether you want exact or approximate ranges. It is optional.
You are not using this argument in your formula, which is fine, as it is optional.
If you omit it, it will default to allowing approximate matches. You typically use that will numeric ranges.

Your explanation isn't 100% clear to me, but I am guessing maybe you want this formula:

=VLOOKUP(A2,'Export Last Week Committed'!$A$2:$I$150,9,FALSE)

Note the lookup range goes from column A to column I. This is what you need if you are matching on column A, but returning the value from column I.
And the 3rd argument is 9 because in the range A:I, column I is the 9th column.

Does that all make sense?