Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP Not working
Hello,
I have a unique VLOOKUP question that I haven't been able to solve with other googling. I have a list that I need to lookup the relevant model names for. The list is both numeric, and alphanumeric. I converted to text, and then sorted so that, for example, 12B1 appeared after 1200, however, I can't get the VLOOKUP to lookup the 12B1 correctly. To further complicate matters, I want it to only look up by the first four digits in the list. If I use the formula: =VLOOKUP(--LEFT(A302,4),ModelName!A$2:D$2001,3,FALSE), it will look up the numerics just fine, but not the alphanumerics. I created a second column to convert the 5 digits to 4, and then did a VLOOKUP on that one for the alphanumeric using this formula: =VLOOKUP(TEXT(B306,"@"),ModelName!A$2:D$2001,3,FAL SE) and that worked. Problem is, each formula won't work on the opposite data type. So, I have a few options. If I can get both of the formulas to only look up using the first four digits, that would be great. If I have to, I can convert the whole column to 4 digits, and do a VLOOKUP on that, but if possible I'd like one formula that I can use on the whole spreadsheet rather than having to apply two different formulas after I've parsed out the data. Help! |
#2
|
|||
|
|||
Quote:
Dear caverchik, Good Afternoon. I believe that you can manage the two situations on the same formula without transform the formula into a nightmare. Try this one: =IF(ISTEXT(A302),VLOOKUP(LEFT(A302,4),ModelName!$A $2:$D$2001,3,FALSE),VLOOKUP(VALUE(LEFT(A302,4)),Mo delName!$A$2:$D$2001,3,FALSE)) Adapt the range or cells to your reality, if necessary. Tell me it it worked for you.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
Quote:
Attach here a sample of your worksheet. Please, put there values that work and values that don´t work to easier to find a solution. We can find a solution as a team.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLOOKUP not working in VBA | Excel Programming | |||
VLookup not working | Excel Worksheet Functions | |||
VLOOKUP not working??? | Excel Worksheet Functions | |||
Vlookup not working | Excel Worksheet Functions |