I am trying to find an easier way to look up values out of Database sheet if certain criteria are met.

Code:

`{=IF(A2=1;INDEX('Source Sheet'!$B$2:$B$18;(MATCH(2;'Source Sheet'!$A$2:$A$18;0)));IF(A2=2;(INDEX('Source Sheet'!$C$2:$C$18;MATCH(3;'Source Sheet'!$A$2:$A$18;0)));IF(A2=3;(INDEX('Source Sheet'!$D$2:$D$18;MATCH(4;'Source Sheet'!$A$2:$A$18;0)));IF(A2=4;(INDEX('Source Sheet'!$E$2:$E$18;MATCH(4;'Source Sheet'!$A$2:$A$18;0)));IF(A2=5;(INDEX('Source Sheet'!$F$2:$F$18;MATCH(4;'Source Sheet'!$A$2:$A$18;0)));0)))))}`

I am aware I could define a custom function within VBA to do different lookups for different criteria (e.g. if there are more than 7) to make it look simpler and if need be I would create one.

For reference, my workbook simplified looks something like this:

Destinationsheet:

Criteria | Lookup for Criteria |

1 | nestled if statement |

2 | nestled if statement |

3 | nestled if statement |

4 | nestled if statement |

5 | nestled if statement |

<tbody>

</tbody>

uuu | vvv | www | xxx | yyy | zzz | aaa |

1 | a | b | c | d | e | abc |

2 | a | b | c | d | e | bcd |

3 | a | b | c | d | e | cde |

4 | a | b | c | d | e | abc |

5 | a | b | c | tra | e | bcd |

6 | a | b | c | d | e | cde |

7 | a | b | c | d | e | abc |

8 | a | b | c | d | e | bcd |

9 | a | b | c | d | e | cde |

5 | a | b | c | d | e | abc |

11 | a | b | c | d | e | bcd |

12 | a | b | c | d | e | cde |

13 | a | b | asdf | d | e | abc |

1 | a | b | c | d | e | bcd |

<tbody>

</tbody>

Does anyone know of a way to do this in an easier way and for more than 7 criteria without using VBA?

Best

Paigan