Nesting Issues

Is there a better way to write the formula below so I can add more. I keep getting an error (more levels of nesting than allowed). It will only hold 8 in a cell.

Thank you in advance for your help.

Demo.


=IF('Vehicle Inspection'!A2="A",'Vehicle Inspection'!C2,IF('Vehicle Inspection'!A2="B",'Vehicle Inspection'!C3,IF('Vehicle Inspection'!A2="C",'Vehicle Inspection'!C4,IF('Vehicle Inspection'!A2="D",'Vehicle Inspection'!C5,IF('Vehicle Inspection'!A2="E",'Vehicle Inspection'!C6,IF('Vehicle Inspection'!A2="F",'Vehicle Inspection'!C7,IF('Vehicle Inspection'!A2="G",'Vehicle Inspection'!C8,IF('Vehicle Inspection'!A2="H",'Vehicle Inspection'!C9))))))))

Try

=INDEX('Vehicle Inspection'!C2:C9,MATCH('Vehicle Inspection'!A2,{"A","B","C","D","E","F","G","H"},0))

Thank you both! They both work but I need to be able to add more boxes for example.

=LOOKUP('Vehicle Inspection'!A2:A200,{"A","B","C","D","E","F","G","H"},'Vehicle Inspection'!C2:C200)

=INDEX('Vehicle Inspection'!C2:C200,MATCH('Vehicle Inspection'!A2:A200,{"A","B","C","D","E","F","G","H"},0))

When I do this it say value is not avaiable to the formula or function. Is there away to add more boxes to same formula?

Thank you