PDA

View Full Version : [SOLVED] Index/Match with multiple criteria



Svmaxcel
09-25-2017, 12:33 AM
Hi all,
I have 2 types of ID, Avaya and Citrix I'd.
I want ID names from them.

Avaya I'd are numbers
Citrix I'd are Alphanumeric

Let's assume column A has IDs
When I receive data from client, column A has IDs, in some cells there are Avaya I'd and in some Citrix I'd, now I want to look up names using those ID.
The confusion does stop there, sometimes the I'd I received has a comma, "-" or a space.
I want to do Index Match but not sure how I can use multiple types of ID.

Example
Col A has ID
Col B should calculate and give me Only Avaya I'd
Col C should calculate and give me Names.

Once I get clean Avaya id in Col B, will be able to do index match

Attaching file for reference.

We have given feedback to client about it and they said it cannot be changed and it might have some typos

Bob Phillips
09-25-2017, 02:22 AM
Try this

=INDEX('Sheet 1'!D:D,MATCH(IFERROR(--SUBSTITUTE(SUBSTITUTE(A2,",","")," ",""),SUBSTITUTE(SUBSTITUTE(A2,",","")," ","")),IF(ISNUMBER(--SUBSTITUTE(SUBSTITUTE(A2,",","")," ","")),'Sheet 1'!B:B,'Sheet 1'!C:C),0))

Svmaxcel
09-25-2017, 05:22 AM
Try this

=INDEX('Sheet 1'!D:D,MATCH(IFERROR(--SUBSTITUTE(SUBSTITUTE(A2,",","")," ",""),SUBSTITUTE(SUBSTITUTE(A2,",","")," ","")),IF(ISNUMBER(--SUBSTITUTE(SUBSTITUTE(A2,",","")," ","")),'Sheet 1'!B:B,'Sheet 1'!C:C),0))

Thanks
Which column should I use for match.
Col B or C.
Col B has Avaya I'd and Col C has Citrix Id

Svmaxcel
09-26-2017, 09:19 AM
Is there any way where I can look up a value from 2 column

Bob Phillips
09-26-2017, 01:38 PM
It is already using the value in A2.