Complicated Nested If statement with 12 conditions and left function

Tune4love

New Member
Joined
Nov 29, 2017
Messages
4
Hello profs., I am having a long table containing about 20,000 rows (format is in numbers having 15 characters long), I need to get only the first 6 digits in the left for each number and then need to do an if statement with 12 conditions so to map each code with its correspondent company name to be as follow noting that i am using office 2013:

553667 it is "Company 1 name"
443213 it is "Company 2 name"
654345 it is "Company 3 name"
986543 it is "Company 4 name"
098837 it is "Company 5 name"
634533 it is "Company 6 name"
847564 it is "Company 7 name"
982474 it is "Company 8 name"
837464 it is "Company 9 name"
263535 it is "Company 10 name"
263637 it is "Company 11 name"
948373 it is "Company 12 name"

Appreciate getting your support and many thanks in advance
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,924
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

One way is to create a lookup table containing each 6 digit code and its corresponding company name.
Then you can just use the VLOOKUP function to get each company name, i.e.
Code:
=VLOOKUP(LEFT(A2,6),LookupTable,2,False)
See here for more on VLOOKUP, if you are not familiar with it: https://www.techonthenet.com/excel/formulas/vlookup.php
 

Tune4love

New Member
Joined
Nov 29, 2017
Messages
4
Hi Joe, it is returning an error when using the below function:

Function: =VLOOKUP(LEFT(E2,6),Sheet1!A:B$,2,FALSE)
fi0iEb

Error Image link:
fi0iEb
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,924
Office Version
  1. 365
Platform
  1. Windows
You cannot post links like that, so I cannot see your error.
What exactly does it say?

In your lookup table, is the first column entered as numbers or text?
Note that in order to compare the two, you can only compare like data types (text to text, or numbers to numbers).
 

Tune4love

New Member
Joined
Nov 29, 2017
Messages
4

ADVERTISEMENT

Hello Joe, when using the formula with the $ sign, it is giving me that formula contains errors.
Without the $ sign, it is not giving any errors but the function returns N/A while it should be the name of the mapped company "Intel"
I checked both columns and they are numbers, please find attached both images URLs for your kind check
hOQm0G
cBgLSw

https://ibb.co/hOQm0G
https://ibb.co/cBgLSw
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,924
Office Version
  1. 365
Platform
  1. Windows
Hello Joe, when using the formula with the $ sign, it is giving me that formula contains errors.
Yes, I missed that. You may a typo there. The absolute range references must be BEFORE the row/column reference. They should never come at the end of a range reference, i.e.
$A - valid
$A$1 -valid
A$ - invalid
A$1$ - invalid

I think the issue is that the LEFT function is a text function, so it returns a text value.
So you either need to do one of two things:
1. Change the values in your lookup table to text
- or -
2. Convert the value you are looking up to a number like this:
Code:
[COLOR=#333333]=VLOOKUP(LEFT(E2,6)+0,Sheet1!A:B,2,FALSE)[/COLOR][IMG]https://ibb.co/fi0iEb[/IMG]
The second one is probably the easiest.
 

Tune4love

New Member
Joined
Nov 29, 2017
Messages
4

ADVERTISEMENT

Still not working. Can you please try it yourself from the screenshots
 

lhartono

Board Regular
Joined
Nov 15, 2017
Messages
103
You can try this...

Code:
=INDEX(Sheet1!$B$2:$B$13,MATCH(LEFT(E2,6),Sheet1!$A$2:$A$13,0))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,924
Office Version
  1. 365
Platform
  1. Windows
Can you please try it yourself from the screenshots
Unfortunately I cannot from my current location. My workplace internet security is pretty strict, and blocks all those sites and does not allow any downloads.

There are tools here that will allow you to post images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Otherwise, it will need to wait until I am home and can view them there.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,924
Office Version
  1. 365
Platform
  1. Windows
Hmmm... I see it now. That is very odd.
I played around, and was able to get it to work like this:
Code:
=VLOOKUP(TEXT(LEFT(E2,6),"000000"),Sheet1!A:B,2,FALSE)
Note that I tested lhartono's solution, and that one appears to work also.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,649
Messages
5,597,368
Members
414,139
Latest member
okela0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top