| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Display a part of a cell in green (or red)... | Excel Worksheet Functions | |||
| display part of the cell content | Excel Discussion (Misc queries) | |||
| Display missing Part Number if Column A does not match column B | Excel Worksheet Functions | |||
| Simple Question, display only part of a date in Number format | Excel Worksheet Functions | |||
| Display Part of the cell value | Excel Worksheet Functions | |||
Linear Mode
