Multiple input values to be matched to return from a single list

[Billy_Kisuke]

Dear All,

Firstly I would like to thank Mr. Excel forum which has solved many complex queries which I had in regards to Excel. Finally I had post a thread for a solution which I didn't find.


[TABLE="width: 221"]
<tbody>[TR]
[TD]Crank shaft[/TD]
[/TR]
[TR]
[TD]Bearing journal - Main[/TD]
[/TR]
[TR]
[TD]Bearing journal - Rear[/TD]
[/TR]
[TR]
[TD]Intermediate bearing journal[/TD]
[/TR]
[TR]
[TD]Crank pin journal[/TD]
[/TR]
[TR]
[TD]Crank gear[/TD]
[/TR]
[TR]
[TD]Counterweights[/TD]
[/TR]
[TR]
[TD]Throw - Front[/TD]
[/TR]
[TR]
[TD]Throw - Rear
[/TD]
[/TR]
</tbody>[/TABLE]

The above is the defined list over a column.

Assume I need to match either 'Crank shaft' or 'Crank gear' while returning its position with a single match formula. Is it feasible to do achieve something like this?

Thanks in advance.

- Billy

 

[Aladin Akyurek]

And what is output - 1 or 6 or 1 and 6?

 

[Billy_Kisuke]

And what is output - 1 or 6 or 1 and 6?

Output should get mapped based on the input, i.e if it is Crankshaft value returned to be 1 & it should be 6 for the crank gear.

- Billy

 

[Aladin Akyurek]

=MATCH($E1,$A$1:$A$9,0)

where E1 houses a value like crank shaft.

 

[Billy_Kisuke]

=MATCH($E1,$A$1:$A$9,0)

where E1 houses a value like crank shaft.

Thanks for the response. However this is not solving the problem.

I will elaborate the requirement.

Now assume E1 = Crankshaft; E2 = Crankgear

In a single formula, I need MATCH function to check for both E1 & E2 at the same time in the list A1:A9. The value 1 & 6 needs to return at cell B1 & B6 accordingly.

Something like =MATCH(OR($E1,$E2),$A$1:$A$9,0), this is returning error. MATCH function is simultaneously looking for either of the inputs in the array to return the corresponding value.

I hope above defined problem is comprehensible now. If there is a solution then it will be extremely helpful for me.

Thanks in advance.

- Billy

 

[Aladin Akyurek]

B1:

=match(e1,$a$1:$a$9,0)

b6:

=match(e2,$a$1:$a$9,0)

 

[Billy_Kisuke]

B1:

=match(e1,$a$1:$a$9,0)

b6:

=match(e2,$a$1:$a$9,0)

Thanks for your patience & response, Aladin.

I think this is an impossible task.

One last try I will make by consolidating & redefining the problem:

Below is the requirement:

Crank shaft
Bearing journal - Main
Bearing journal - Rear
Intermediate bearing journal
Crank pin journal
Crank gear
Counterweights
Throw - Front
Throw - Rear


Assume this list in column A & the parameters will change randomly within the column. As & when the parameters change I need a solution which involves MATCH function.

I will elaborate the requirement.

Now assume E1 = Crankshaft; E2 = Crankgear

In a single formula, I need MATCH function to check for both E1 & E2 at the same time in the column A. The value which matches with the text string should return accordingly in column B i.e in the present case, 1 in B1 & 6 in B6. When the Crankshaft & Crankgear cell location change within column A, corresponding value needs to be returned in Column B. This should occur with a single formula only which I am emphasizing over & over (sorry for being repetitive & if it is sounding rude / arrogant).

I tried this formula in Column B:
=MATCH(OR($E1,$E2),$A$1:$A$9,0)
MATCH function is simultaneously looking for either of the inputs in the array to return the corresponding value (trying to look for E1 or E2 & return the value in Column B for individual matches). However, this is returning an error.

I hope above defined problem is comprehensible now. If there is a solution then it will be extremely helpful for me.

Thanks in advance.

P.S - The list which is existing is really huge.

- Billy