If-else statements for multiple columns

[R3da1ert]

Hi,

Need a formula for showing value of a cell as follows:

if cell value = value in cell a1, then show value in cell b1, else if cell value = value in cell a2, then show value in cell b2, else if cell value = value in cell a3, then show value in cell b3 and so on.

[TABLE="class: outer_border, width: 250"]
<tbody>[TR]
[TD]Value (input)[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ABC[/TD]
[TD]01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]LMN[/TD]
[TD]03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PQR[/TD]
[TD]02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]XYZ[/TD]
[TD]01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TUV[/TD]
[TD]04[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I created a manual formula which is as follows:
=IF(E4=P4, Q4,IF(E4=P5,Q5,IF(E4=P6,Q6,IF(E4=P7,Q7,IF(E4=P8,Q8,IF(E4=P9,Q9,IF(E4=P10,Q10,IF(E4=P11,Q11,IF(E4=P12,Q12,IF(E4=P13,Q13,IF(E4=P14,Q14,IF(E4=P15,Q15,IF(E4=P16,Q16,IF(E4=P17,Q17,IF(E4=P18,Q18,IF(E4=P19,Q19,IF(E4=P20,Q20,IF(E4=P21,Q21,IF(E4=P22,Q22,IF(E4=P23,Q23,IF(E4=P24,Q24,IF(E4=P25,Q25,IF(E4=P26,Q26,IF(E4=P27,Q27,IF(E4=P28,Q28,IF(E4=P29,Q29,IF(E4=P30,Q30,IF(E4=P31,Q31,IF(E4=P32,Q32,IF(E4=P33,Q33,IF(E4=P34,Q34,ERROR)))))))))))))))))))))))))))))))

This seems to work for 34 entries but I will have more than 100 entries and every time a new entry is added, the whole formula has to be edited (and the formula cannot be dragged for the "Values (input)" column, meaning I have to write the formula for every cell beginning with condition "IF E5=...", "IF E6=..." and so on. Please let me know if there is a shortcut.

Thanks and regards,

Akshay

 

[dave3009]

Try this instead of the multiple IF

=IFERROR(INDEX($Q$4:$Q$100, MATCH(E4, $P$4:$P$100)), "ERROR")

That can be dragged down to reference E5, E6 etc and will still return Q where it matches E in P

 

[dave3009]

Or

=IFERROR(VLOOKUP(E4, $P$4:$Q$100, 2, 0), "ERROR")

Though I think the INDEX is preferable.