Adding formula segments together

[JDSOuth49]

I am needing help with the following formula:

=IF(A65=Sheet1!A2,Sheet1!C2),or IF(A65=Sheet1!A3,Sheet1!C3),or IF(A65=Sheet1!A4,Sheet1!C4),or IF(A65=Sheet1!A5,Sheet1!C5)

The 1st segment works great, but not when the formula segments are added

 

[arthurbr]

Perhaps

=INDEX(Sheet2!C2:C5,match(A65,Sheet1!A2:A5,0))

 

[Cubist]

One option is

=SWITCH(A65,Sheet1!A2,Sheet1!C2,Sheet1!A3,Sheet1!C3,Sheet1!A4,Sheet1!C4,Sheet1!A5,Sheet1!C5)

It looks like a continuous range so you can a VLOOKUP

=VLOOKUP(A65,Sheet1!$A$2:Sheet1!$C$5,3)

or XLOOKUP.

XLOOKUP(A65,Sheet1!$A$2:$A$5,Sheet1!$C$1:Sheet1!$C$5)

 

[JDSOuth49]

One option is

=SWITCH(A65,Sheet1!A2,Sheet1!C2,Sheet1!A3,Sheet1!C3,Sheet1!A4,Sheet1!C4,Sheet1!A5,Sheet1!C5)

It looks like a continuous range so you can a VLOOKUP

=VLOOKUP(A65,Sheet1!$A$2:Sheet1!$C$5,3)

or XLOOKUP.

XLOOKUP(A65,Sheet1!$A$2:$A$5,Sheet1!$C$1:Sheet1!$C$5)

Worked great. Thank you.

 

[arthurbr]

@JDSOuth49 You did not even mention my solution? Was it wrong or not sophisticated enough ?