#1   Report Post  
Old November 15th 08, 06:01 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 30
Default display part 2


SHEET 1 SHEET 2

A A B

2011 | 204200000 LOGO
2015 | 201500000 AGEN
2022 | 201100000 STAR
2032 | 205400000 DURA
2034 | 203200000 WRAP
2035 | 203500000 WORK
2042 | 202200000 GROU
2054 | 203400000 ALTI

I'm trying to display the values in column B (Sheet 2) next to the values in
column A (Sheet 1) by using the 1st 4 digits in column A (Sheet 2).

  #2   Report Post  
Old November 15th 08, 06:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default display part 2

If the numbers you have in sheet2 column A always end with 00000, you could use:

=VLOOKUP(A1*100000,Sheet2!A:B,2,FALSE)


GAIDEN wrote:

SHEET 1 SHEET 2

A A B

2011 | 204200000 LOGO
2015 | 201500000 AGEN
2022 | 201100000 STAR
2032 | 205400000 DURA
2034 | 203200000 WRAP
2035 | 203500000 WORK
2042 | 202200000 GROU
2054 | 203400000 ALTI

I'm trying to display the values in column B (Sheet 2) next to the values in
column A (Sheet 1) by using the 1st 4 digits in column A (Sheet 2).


--

Dave Peterson
  #3   Report Post  
Old November 15th 08, 07:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 747
Default display part 2

try this

=INDEX(Sheet2!B1:B10,MATCH(TRUE,EXACT(A1,LEFT(Shee t2!A1:A10,4)),0))

this is an array function, use Ctrl + shift + enter



On Nov 15, 10:01*am, GAIDEN wrote:
SHEET 1 * * * * SHEET 2

A * * * * * * * A * * * * * * * B

2011 * *| * * * 204200000 * * * * * * * * * * * LOGO
2015 * *| * * * 201500000 * * * * * * * * * * * AGEN
2022 * *| * * * 201100000 * * * * * * * * * * * STAR
2032 * *| * * * 205400000 * * * * * * * * * * * DURA
2034 * *| * * * 203200000 * * * * * * * * * * * WRAP
2035 * *| * * * 203500000 * * * * * * * * * * * WORK
2042 * *| * * * 202200000 * * * * * * * * * * * GROU
2054 * *| * * * 203400000 * * * * * * * * * * * ALTI

I'm trying to display the values in column B (Sheet 2) next to the values in
column A (Sheet 1) by using the 1st 4 digits in column A (Sheet 2).


  #4   Report Post  
Old November 15th 08, 11:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 30
Default display part 2

What if the numbers were followed by letters?

"Dave Peterson" wrote:

If the numbers you have in sheet2 column A always end with 00000, you could use:

=VLOOKUP(A1*100000,Sheet2!A:B,2,FALSE)


GAIDEN wrote:

SHEET 1 SHEET 2

A A B

2011 | 204200000 LOGO
2015 | 201500000 AGEN
2022 | 201100000 STAR
2032 | 205400000 DURA
2034 | 203200000 WRAP
2035 | 203500000 WORK
2042 | 202200000 GROU
2054 | 203400000 ALTI

I'm trying to display the values in column B (Sheet 2) next to the values in
column A (Sheet 1) by using the 1st 4 digits in column A (Sheet 2).


--

Dave Peterson

  #5   Report Post  
Old November 15th 08, 11:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default display part 2

=index(sheet2!b$1:b$99,match(a1&"",left(sheet2!a$1 :a$99,4),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.



GAIDEN wrote:

What if the numbers were followed by letters?

"Dave Peterson" wrote:

If the numbers you have in sheet2 column A always end with 00000, you could use:

=VLOOKUP(A1*100000,Sheet2!A:B,2,FALSE)


GAIDEN wrote:

SHEET 1 SHEET 2

A A B

2011 | 204200000 LOGO
2015 | 201500000 AGEN
2022 | 201100000 STAR
2032 | 205400000 DURA
2034 | 203200000 WRAP
2035 | 203500000 WORK
2042 | 202200000 GROU
2054 | 203400000 ALTI

I'm trying to display the values in column B (Sheet 2) next to the values in
column A (Sheet 1) by using the 1st 4 digits in column A (Sheet 2).


--

Dave Peterson


--

Dave Peterson


  #6   Report Post  
Old November 16th 08, 08:39 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 30
Default display part 2

Thanks,

That worked.

"Dave Peterson" wrote:

=index(sheet2!b$1:b$99,match(a1&"",left(sheet2!a$1 :a$99,4),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.



GAIDEN wrote:

What if the numbers were followed by letters?

"Dave Peterson" wrote:

If the numbers you have in sheet2 column A always end with 00000, you could use:

=VLOOKUP(A1*100000,Sheet2!A:B,2,FALSE)


GAIDEN wrote:

SHEET 1 SHEET 2

A A B

2011 | 204200000 LOGO
2015 | 201500000 AGEN
2022 | 201100000 STAR
2032 | 205400000 DURA
2034 | 203200000 WRAP
2035 | 203500000 WORK
2042 | 202200000 GROU
2054 | 203400000 ALTI

I'm trying to display the values in column B (Sheet 2) next to the values in
column A (Sheet 1) by using the 1st 4 digits in column A (Sheet 2).

--

Dave Peterson


--

Dave Peterson



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
Display a part of a cell in green (or red)... Anne P Excel Worksheet Functions 3 May 15th 08 02:21 PM
display part of the cell content linda Excel Discussion (Misc queries) 4 August 28th 07 03:02 AM
Display missing Part Number if Column A does not match column B Erik T Excel Worksheet Functions 2 April 17th 06 11:23 PM
Simple Question, display only part of a date in Number format DB Explorer Excel Worksheet Functions 6 March 17th 06 12:47 PM
Display Part of the cell value ramana Excel Worksheet Functions 6 November 17th 05 10:34 AM


All times are GMT +1. The time now is 06:41 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017