Index Match Concatenate

[Guest]

Here is a problem I'm facing currently while trying to prepare a wiring and
termination chart in excel where I need to automatically deduce cable IDs
from connector IDs using concatenate function.

Data from two columns ID1 and ID2 are to be concatenated in column CONC but
with an exception. Let's take row 7 with A=8, B=4 and C=Cell C7. The formula
should search in the range C2:C6 and if it finds the result of the function
"=concatenate(B7,A7)", it should return the same (which should be 48 in this
case), otherwise should return the result of "=concatenate(A7,B7) in cell
C7,i.e. 84. In Col D I have listed the values that I desire to be returned by
the formula in col C.

ID1 ID2 CONC DESIRED VALUES
1 9 #N/A 19
2 8 #N/A 28
3 7 #N/A 37
4 6 #N/A 46
5 5 #N/A 55
8 4 #N/A 84
7 3 #N/A 37
8 2 #N/A 28
9 1 #N/A 19

The formula I used is:
=IF(INDEX($C$1:C6,MATCH(CONCATENATE(B7,A7),($C$1:C6),0)),CONCATENATE(B7,A7),CONCATENATE(A7,B7))

I keep getting an error #N/A though at one time I did get a correct answer.
Don't know what happened? Any help please?

 

[arno]

match(concatenate(a1,a2)) will search for a TEXT and will find nothing
in a list of NUMBERS. change your search value to a NUMBER like

value(concatenate(a1,a2))

arno

 

[Bob Phillips]

=IF(ISNA(MATCH(CONCATENATE(B2,A2),$C$1:C1,0)),CONCATENATE(B2,A2),INDEX($C$1:
C1,MATCH(CONCATENATE(A2,B2),$C$1:C1,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

try this one:

=IF(ISERROR(INDEX($C$1:C6,MATCH(B7&A7,$C$1:C6,0))),B7&A7,A7&B7)

 

[Guest]

Sorry gentlemen and especially Peter Atherton, but none of your suggestions
gave my desired results. I continued to get either #N/A or just a concatenate
/ and function result.

Any other suggestions?

 

[Bob Phillips]

Not without knowing why they didn't work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Can't say. All I can tell you is this is the result of your formula:

ID1 ID2 BOB DESIRED VALUES
1 9 91 19
2 8 82 28
3 7 73 37
4 6 64 46
5 5 55 55
8 4 48 84
7 3 37 37
8 2 28 28
9 1 19 19

 

[Bob Phillips]

Maybe this

=IF(ISNA(MATCH(B2&A2,$C$1:C1,0)),A2&B2,B2&A2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks a lot Bob. It worked.
--
Sumit

Maybe this

=IF(ISNA(MATCH(B2&A2,$C$1:C1,0)),A2&B2,B2&A2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)