filling cells acording to the first 3 numbers

  • Thread starter Thread starter pmarques
  • Start date Start date
P

pmarques

How can the following be made (excel 97 SR -2):

in collumm A i have numbers (212021740, ...) i want to fill de collumms
B and C
acording to the first 3 numbers (212).
Collumms B and C must be automaticaly fill in agreement with some
rules
ex: 212XXXXXX Name1 code1
239XXXXXX Name2 code2
241XXXXXX Name3 code3
.... ... ...

Thanks :-)


+-------------------------------------------------------------------+
|Filename: Relat.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3795 |
+-------------------------------------------------------------------+
 
Try these formulas in cells B5 and C5 respectively:-

=VLOOKUP(VALUE(LEFT(A5,3)),$E$5:$G$7,2,FALSE)
=VLOOKUP(VALUE(LEFT(A5,3)),$E$5:$G$7,3,FALSE)

Copy them down to cells B6:B474 and C6:C474

Note you will get #N/A's where the code doesn't exist in the Example
table E5:G7
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting number of days up to an empty cell then start over again 1
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
Extracting Data from a Database, but skipping rows 3
How to find the Average of cells containing a value 1
Returning other cells with the same value in a column, but different row. 1
How to find the first non-blank number in a row? 7
Using Sheets.Add after and want to keep a sheet as the first sheet 2
The only way I know to ask this - with an example -- Counting & Inserting (Easy) 7

Back
Top