Picking a Option from a cell by a value in ms excel-CodePudding

i'm looking for a formula where i can copy text from A to D option according to the option text in Ans column. see the attachment for more information.

if the Ans column has value B than value from option B should print in AnsText Column and so on.

CodePudding user response：

Try any of following formulas.

=INDEX(B4:E4,MATCH(F4,B3:E3,0))
=XLOOKUP(F4,B3:E3,B4:E4)
=FILTER(B4:E4,B3:E3=F4)
=HLOOKUP(F4,B3:E4,2,FALSE)

CodePudding user response：

If A, B, C and D are always going to be in alphabetical order, then the lookup function will work best.

=LOOKUP(E2,A1:D1,A2:D2)

If not, the the HLOOKUP will need to be used

=HLOOKUP(E2,A1:D2,2,FALSE)