Figuring out a lengthy formula. Probably quite simple...

[Tom Battaglia]

I have the situation similar to the following (though simplified)

Sheet 1 Sheet 2
Row 1 Column A Column B Column A Column B
Row 2 11a ??? 22 b
Row 3 22a ??? 33 c
Row 4 33a ??? 44 d
Row 5 44a ??? 11 a

Minus the suffix 'a' in Sheet 1 Column A cells, these Column A cells equal the Sheet 2 Column A cells, though in a different order.

I am looking for a formula to use in Sheet 1 Column B, which finds the corresponding cell of Sheet 1 Column A in Sheet 2 Column A, and equals the corresponding cell in Sheet 1 Column B.

Though it would make the formula less "busy", I do want to avoid having to make another column (e.g. =LEFT(A1:A5,2) ). I should also point out that there are many more rows in the Sheet 1 columns than in Sheet B.

e.g. The result would look like the following:

Sheet 1 Sheet 2
Row 1 Column A Column B Column A Column B
Row 2 11a a 22 b
Row 3 22a b 33 c
Row 4 33a c 44 d
Row 5 44a d 11 a


The solution would probably use a mix of LEFT and MATCH functions, but I can't quite work it out. Apologies if the explanation is a little over-convoluted.

Thanks!

Tom

 

[Tom Battaglia]

I have the situation similar to the following (though simplified)

Sheet 1 Sheet 2
Row 1 Column A Column B Column A Column B
Row 2 11a ??? 22 b
Row 3 22a ??? 33 c
Row 4 33a ??? 44 d
Row 5 44a ??? 11 a

Minus the suffix 'a' in Sheet 1 Column A cells, these Column A cells equal the Sheet 2 Column A cells, though in a different order.

I am looking for a formula to use in Sheet 1 Column B, which finds the corresponding cell of Sheet 1 Column A in Sheet 2 Column A, and equals the corresponding cell in Sheet 1 Column B.

Though it would make the formula less "busy", I do want to avoid having to make another column (e.g. =LEFT(A1:A5,2) ). I should also point out that there are many more rows in the Sheet 1 columns than in Sheet B.

e.g. The result would look like the following:

Sheet 1 Sheet 2
Row 1 Column A Column B Column A Column B
Row 2 11a a 22 b
Row 3 22a b 33 c
Row 4 33a c 44 d
Row 5 44a d 11 a


The solution would probably use a mix of LEFT and MATCH functions, but I can't quite work it out. Apologies if the explanation is a little over-convoluted.

Thanks!

Tom

Just noticed the spaces didn't quite work out... apologies again...

 

[GS]

Have a look in Help at the VLOOKUP() function! Pay attention to how the
last arg works when set True.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Tom,

Am Wed, 3 Dec 2014 21:07:51 -0800 (PST) schrieb Tom Battaglia:

Sheet 1 Sheet 2
Row 1 Column A Column B Column A Column B
Row 2 11a ??? 22 b
Row 3 22a ??? 33 c
Row 4 33a ??? 44 d
Row 5 44a ??? 11 a

Minus the suffix 'a' in Sheet 1 Column A cells, these Column A cells equal the Sheet 2 Column A cells, though in a different order.

if your numbers always have 2 digits try in B1:
=VLOOKUP(--(LEFT(A1,2)),Sheet2!$A$1:$B$100,2,0)

If your numbers have different length try in B1:
=VLOOKUP(LOOKUP(9^9,1*LEFT(A1,COLUMN(1:1))),Sheet2!$A$1:$B$100,2,0)
and insert the array formula with CTRL+Shift+Enter


Regards
Claus B.