Vlookup not working as expected

icecold - Aug 6, 2011 at 10:31 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 7, 2011 at 01:26 PM

Sorry for my last post I didn't mean to be rude, just posted technical info, so can you please help me?

I tried to figure out this by myself, but I lack the knowledge and expertise with Excel functions.
So I would really appreciate any help or guidance on the subject. I read many examples and for some reason my function shows empty N/A results ...

What I'm trying to accomplish is to use vlookup to look for matching values, but somehow I am missing something as the function is not performing what I expected ...

After watching many videos and read many examples I decided to do this:

copy my workbook to another spreadsheet within the same workbook, therefore now I have my base sheet + products all under the same workbook, I made my base spreadsheet with just too columns and over 57000 rows, the first column named ID, is where I placed my vlookup function, the second column is where I look up, and my lookup table is in products, the second, spreadsheet, this look up table is virtually identical except for the fact it has all values populated.

The model is like this

base spreadsheet two columns placed under A1 and B1
ID model
so under my A2 column I placed this function
to look up the B2 model and search my products lookup table, under column 1, ID.

Then I added a false to look for an exact match.
For some reason this is not working, even after filtering the data with TrimALL() a macro I found to trim all unexpected hidden codes.

So please tell me what might be wrong from my approach or what you can suggest.

Thank you!

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 7, 2011 at 06:46 AM
I think you might be a little confused over use of VLOOKUP

VLOOKUP(A2, 'Sheet1'!B2:z100, 9, false)

A2: the value that i want to compare

'Sheet1'!B2:z100 : is the range where it should do the compare and return me the associated cell value when there is a match. Here is one thing that I think you are missing. In the range where you want to compare, the first column is used to perform the match. So It will look for A2 in range Sheet1!B2:B100.

9: Once there is a match, it will return you the value in the 9th column in the range B2:Z100, that would be a cell from column J

False: that match needs to be exact.

Now if your match column exists after the column whose you value you want to return, then you cannot directly use vlookup. In that case either you have to make use of some temp column to ensure that match column occurs before return column. If that isnot possible, you would have to make use of
INDIRECT and MATCH functions together. First you would use

INDIRECT("'Sheet1'!A" & MATCH(A2, 'Sheet1'!B:B, 0))
The match part will return the row number where the match occurred, The use that row number in indirect function to get the cell value from column A.
Well yes I am confused, because I watched many videos, and I couldn't spot the difference between what they did and what I do, I went through all the procedure step by step.

So what you said is vlookup is not what I have to use in this case?

I do have identical columns in both sheets Id and Model, except ID in base, is empty, so I need to match the models.

like this in my lookup table I have DH0004(model name), ID 23456, and I also have the same model name in the base sheet. DH0004, so what I want is vlookup to check for the match and the result should be 23456 ... the difference here is not all the models are present in my base table, otherwise it will be a simpler cut and paste.

Please note everything is under the same workspace, different sheets ...
Solved! partially I had to move the columns to a new sheet and there created 4 columns the first two were the lookup table, the other two, had model populated and under ID I entered the following:

=INDIRECT("B" &(MATCH(D2,A$2:A$56317,0)+1))

and it worked!

Thanks for the tips that helped me find out this solution, although it was weird but I had to write the columns with model first and ID second, the other group following the same pattern, I tried other combinations, and writing another spreadsheet with the same table never worked!
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 7, 2011 at 01:26 PM
Looking at your working solution,
I would think that this should work

=VLOOKUP(D2, $A$2:$B$56317, 2, false)