Index/Match with multiple criteria

[Svmaxcel]

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]

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]

Try this

=INDEX('Sheet 1'!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]

Is there any way where I can look up a value from 2 column

[Bob Phillips]

It is already using the value in A2.