Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Des
 
Posts: n/a
Default How do I lookup a corresponding value in another table

I am trying to create a lookup where I am entering a 6 account number digit
but my 2 col lookup database has 16 number digits in col 1. I want my
lookup to ignore the last 10 digits in this table and give me the
corresponding text from col2 in table.
  #2   Report Post  
Barb Reinhardt
 
Posts: n/a
Default How do I lookup a corresponding value in another table

I'd use a helper column that gets the last 6 characters of the column 1
data.

Use
=right(a1,6)

"Des" wrote in message
...
I am trying to create a lookup where I am entering a 6 account number digit
but my 2 col lookup database has 16 number digits in col 1. I want my
lookup to ignore the last 10 digits in this table and give me the
corresponding text from col2 in table.



  #3   Report Post  
Roger Govier
 
Posts: n/a
Default How do I lookup a corresponding value in another table

Hi Des

One way
Insert a column to the right of your the column with your 16 digit numbers.
Use the formula =--LEFT(A1,6) where A is equal to the column letter
ocontaining your 16 digit number. Copy down.
Make this new column the leftmost column of the range for your lookup.

Note the diouble unary minus -- is necessary to make the values into
numbers, otherwise you will be comparing your lookup number against text and
getting a #N/A answer.

--
Regards

Roger Govier


"Des" wrote in message
...
I am trying to create a lookup where I am entering a 6 account number digit
but my 2 col lookup database has 16 number digits in col 1. I want my
lookup to ignore the last 10 digits in this table and give me the
corresponding text from col2 in table.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create a lookup table with an added varable? GIZZMO Excel Worksheet Functions 3 April 6th 09 10:32 PM
Lookup table? Peter Excel Discussion (Misc queries) 1 September 17th 05 08:02 PM
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM
Multiple table lookup KG Excel Discussion (Misc queries) 1 June 3rd 05 05:39 AM
How do I lookup a table from right to left ? Daz9 Excel Worksheet Functions 1 April 11th 05 11:21 AM


All times are GMT +1. The time now is 03:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"