match text

[nikhil0311]

currency help.xlsx
	A	B	C
1	Sr No.	Currency	Output
2	1	USDAUD	USDAUD
3	2	GBPUSD	GBPUSD
4	3	AUDUSD	USDAUD
5	4	GBPAUD	GBPAUD
6	5	INRUSD	INRUSD
7	6	USDGBP	GBPUSD
Sheet1

Hello friends, hope you are doin well. The data is there in column B and I want the output in column C.

I am looking to identify similar currency combination but they are not in similar format
For instance - USDAUD is one format & AUDUSD is another format. Output should be either USDAUD or AUDUSD in column C. Let me know if this is possible? Thanks!

 

[Trebor76]

Hi nikhil0311,

If I've understood correctly (big if here) put this formula in cell C2...

=IFERROR(VLOOKUP(RIGHT(B2,3)&LEFT(B2,3),$B$2:B2,1,FALSE),B2)

...and copy down as far as needed.

Regards,

Robert

 

[nikhil0311]

Hi nikhil0311,

If I've understood correctly (big if here) put this formula in cell C2...

=IFERROR(VLOOKUP(RIGHT(B2,3)&LEFT(B2,3),$B$2:B2,1,FALSE),B2)

...and copy down as far as needed.

Regards,

Robert

Exactly what I was looking for, Sir. Thank you so much!!

 

[Peter_SSs]

Exactly what I was looking for

Checking ...
Does that mean the values cannot occur again further down the column? For example below I have 3 rows that contain one of the USD/AUD combinations but the suggested formula in column C returns two different values for that combination.

If that is an issue for you, would always putting the two currencies in alphabetical order (column D) be any use to you?

24 08 12.xlsm
	A	B	C	D
1	Sr No.	Currency	Output
2	1	USDAUD	USDAUD	AUDUSD
3	2	GBPUSD	GBPUSD	GBPUSD
4	3	AUDUSD	USDAUD	AUDUSD
5	4	GBPAUD	GBPAUD	AUDGBP
6	5	INRUSD	INRUSD	INRUSD
7	6	USDGBP	GBPUSD	GBPUSD
8	7	USDAUD	AUDUSD	AUDUSD
Currencies

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(VLOOKUP(RIGHT(B2,3)&LEFT(B2,3),$B$2:B2,1,FALSE),B2)
D2:D8	D2	=MID(B2&B2,1+3*(RIGHT(B2,3)<LEFT(B2,3)),6)

 

[Trebor76]

Exactly what I was looking for, Sir. Thank you so much!!

You're welcome. Please also note Peter's comments.