Hello everybody,
I am trying to find an easier way to look up values out of Database sheet if certain criteria are met.
My boss is convinced there is a formula built in to excel. Before I tell him a defenite "No" I want to make really sure there is no simpler way.
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:
<tbody>
</tbody>Sourcesheet:
<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
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)))))}
My boss is convinced there is a formula built in to excel. Before I tell him a defenite "No" I want to make really sure there is no simpler way.
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