CODEWITHSUNDEEP
excelexcel-formulaexcel-2010excel-2007vba7
Anthony Ifeanyi
Anthony Ifeanyi

Reputation: 53

If two cells match, return value from third in the same row irrespective of the order in excel

I have a table as follows:

|**Corresponding Values**|
|:.......|:.....:|......:|
|   O1   |   F1  |   S1  |
|   O2   |   F2  |   S2  |
|   O3   |   F3  |   S3  |
|   O4   |   F4  |   S4  |
|   O5   |   F5  |   S5  |
|   O6   |   F6  |   S6  |
|   O7   |   F7  |   S7  |

I want to get the corresponding third value if given any two values (irrespective of the order/sequence) in the same row; example

as shown on the second column from the left in the attached image.

I tried the following code for (O6 and F6) and got S6; but it doesn't work in the reverse order ie. (F6 and O6) despite using the OR Function.

=(INDEX(Data!O2:O8,MATCH(B8,IF(Data!N2:N8=B11,Data!M2:M8),0)))

=OR((INDEX(Data!O2:O8,MATCH(B8,IF(Data!N2:N8=B11,Data!M2:M8),0))), (INDEX(Data!N2:N8,MATCH(B8,IF(Data!O2:O8=B11,Data!M2:M8),0))), (INDEX(Data!M2:M8,MATCH(B8,IF(Data!O2:O8=B11,Data!N2:N8),0))),(INDEX(Data!O2:O8,MATCH(B8,IF(Data!M2:M8=B11,Data!N2:N8),0))), (INDEX(Data!N2:N8,MATCH(B8,IF(Data!M2:M8=B11,Data!O2:O8),0))), (INDEX(Data!M2:M8,MATCH(B8,IF(Data!N2:N8=B11,Data!O2:O8),0))))

Pls note on the image, the second column from the left shows these values.

Result

Data Sheet

Kindly guide me on the best approach.

Upvotes: 1

Views: 1187

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

=LOOKUP(1,0/(MMULT((Data!M8:N14=B8)+(Data!M8:N14=B11),{1;2})=3),Data!O8:O14)

Add an error clause for the case where there are no matching rows if desired.

Upvotes: 2

Related Questions