Vlookup not working as expected
Solved/Closed
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 7, 2011 at 01:26 PM
Related:
- Vlookup not working as expected
- Shift + number keys not giving expected symbols - Guide
- Macro gives me expected en sub error - Excel Forum
- Vlookup to find missing data in 2 columns - Excel Forum
- Vlookup date 1/0/1900 ✓ - Excel Forum
- Transfer data from one excel worksheet to another automatically vlookup - Guide
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
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.
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 ...
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!
=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
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)
I would think that this should work
=VLOOKUP(D2, $A$2:$B$56317, 2, false)
