display part 2

  • Thread starter Thread starter GAIDEN
  • Start date Start date
G

GAIDEN

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).
 
If the numbers you have in sheet2 column A always end with 00000, you could use:

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

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

this is an array function, use Ctrl + shift + enter
 
What if the numbers were followed by letters?

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

=VLOOKUP(A1*100000,Sheet2!A:B,2,FALSE)
Click to expand...
 
=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.
 
Thanks,

That worked.

Dave Peterson said:
=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.
Click to expand...
 
Back
Top