Index Match Concatenate

S

Sumit

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?
 
A

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
 
B

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)
 
M

Muhammed Rafeek M

try this one:

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

Sumit

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?
 
B

Bob Phillips

Not without knowing why they didn't work.

--
HTH

Bob Phillips

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

Sumit

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
 
B

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)
 
S

Sumit

Thanks a lot Bob. It worked.
--
Sumit


Bob Phillips said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top