new cell in excel with data from other sheet

  • Thread starter Thread starter M.K
  • Start date Start date
M

M.K

hi all

if i have coulm in excel include the followinf dat

334567778
224567890
334567890
123456765
678123678

and i want include new coulm with the following detales

if th first 4 digt is 3345 the cell will be from uae
but if the first 4 digt is 2245 the cell will be from USA
and if first 4 digt is 6781 the cell will from QAT
if the first 4 digt is 6781 the cekk will be from SA



334567778 from uae
224567890 from USA
334567890 from uae
123456765 from SA
678123678 from QAT



how i do that
 
You can use nested IF if you have only four countries...
Otherwise in another sheet (say Sheet2)
enter the numbers in Col A and corresponding country in Col B
then assuming your numbers are in Col A of Sheet1, enter this in Col B of
Sheet1
=VLOOKUP(LEFT(A1,4),Sheet2!A:B,2,FALSE)
and copy down...
 
Hi,

Your 3rd and 4th numbers are the same, I'm assuming this is an error:
"if th first 4 digt is 3345 the cell will be from uae
but if the first 4 digt is 2245 the cell will be from USA
and if first 4 digt is 6781 the cell will from QAT
if the first 4 digt is 6781 the cekk will be from SA"
Click to expand...

The most flexible way would be to use something like this which could be set
up to handle any number of prefixes:

1. Create a table like this in say G1:H4

3345 UAE
2245 USA
6781 QAT
1234 SA

Then your formula is

=VLOOKUP(--LEFT(A1,4),$G$1:$H$4,2,)

Where the data is in A1:A1000.
 
=IF(LEFT(A1,4)="3345","uae",IF(LEFT(A1,4)="2245","usa",IF(LEFT(A1,4)="6781","qat","sa")))


Gord Dibben MS Excel MVP
 
You could either setup a table (Say in D2:E5) with your correlations
3345 uae
2245 USA
6781 QAT
1234 SA


Then your formula could be:
=VLOOKUP(VALUE(LEFT(A2,4)),$D$2:$E$5,2,FALSE)
and copy down as needed.

The benefit of this setup si that you could easily add additional entries to
your table as needed.
 
Back
Top