I have a set of data that has 3 column of data
I would like vlookup or some function that returns the value of the first column when it finds the item I am referencing in the 3rd column.
When I use this formula
=VLOOKUP(A17,$test.I23:$test.K38,1,0)
I get #N/A
Obviously A17 is the current sheet and $test is another sheet.
I hope this makes sense.
Does the cell above have a formula =VLOOKUP(A16,$test.I22:$test.K37,1,0)?
You need to make your array absolute, I don’t know the start of your array so you will need to correct that but the formula should have some $ in, such as =VLOOKUP(A17,test.$I$14:test.$K$29,1,0). You can enter the $ by clicking after cell address, $test.I23and then pressing F4
I changed it to:
=VLOOKUP(A17,$test.$I$23:$test.$K$38,1,0)
But it still returned #N/A
The start of the array is $test.$I$23 as shown in the formula.
I’m thinking the value of A17 is not being found due to some formatting issue.
Let me do some research and post back
Edit 1:
The data matched so it should have been found fine.
I guessed at the most likely cause given the lack of absolute addressing, hence the starting cell of I4.
Is the ending cell of the array the end of the data that you want to lookup?
Please post a sample
Because you want to retrieve the result from a column to the left of search array then you cannot use VLOOKUP. Use XLOOKUP instead, =XLOOKUP(A17;test.$K$19:test.$K$27;test.$I$19:test.$I$27)
test129466EA.ods (9.1 KB)
1 Like
That will work. Thanks