1

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

  • (O5 and F5) or (F5 and O5) gives S5
  • (F7 and S7) or (S7 and F7) gives O7
  • (O3 and S3) or (S3 and O3) gives F3
  • and other possible permutations (any order) to give their corresponding third value, except (O and O) or (F and F) and (S and S) ie. Cell B8 and B11 must not be the same,

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.

Anthony Ifeanyi
  • 53
  • 5

1 Answers1

2

=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.

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • Nice work @Jos Woolley, your formular works for only (O and F) or (F and O) to get S but I needed it to work for all possible permutations except (O and O), (F and F) and (S and S). – Anthony Ifeanyi Mar 07 '22 at 19:00
  • Not sure what you mean. It works for whatever pair of entries there are in B8 and B11. If that's not what you wanted then you'll need to update your post with clearer examples and expected results. – Jos Woolley Mar 07 '22 at 19:05
  • I hope the update gives more clarity. – Anthony Ifeanyi Mar 07 '22 at 19:29
  • I'm afraid not, at least to me. I still don't see why my solution doesn't do precisely as you ask. – Jos Woolley Mar 07 '22 at 20:24