# Easier formula for nestled if statement

#### Paigan

##### New Member
Hello everybody,

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>
Sourcesheet:
 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

#### Eric Golf

##### Active Member
Hi,

Untested but I think below might be what you are looking for, using the value in column A both to lookup & as return column

Code:
``=VLOOKUP(\$A2,'Source Sheet'!\$A\$2:\$F\$18,\$A2+1,0)``
Hope this helps,

Eric.

#### anglais428

##### Well-known Member
What is the expected results in the Lookup criteria column?
It looks like you could use Vlookup

#### Paigan

##### New Member
@Eric Thanks, your formula works like a charm for the examplesheet, however unfortunately the return columns and criteria will not be ordered so neatly and have such straigthforward values. They might even be text.

@Anglais428 the expected results would be:
 Criteria Lookup for Criteria (result) 1 a 2 b 3 c 4 d 5 e

<tbody>
</tbody>

#### pjmorris

##### Well-known Member
Hi haven't tried this directly, but have you investigated DGET one of its arguments is a range of cells the specify the criteria to be used. It might be helpful.

Regards

#### Paigan

##### New Member
Peter,

yes I have, as I have not yet used the DGET formula I may well be mistaken here, but doesn't that still pose the same problem.
Ofcourse I could use DGET to get a value for ONE specific criterion, but I would still have to nest functions to tell my cell how to behave for different criteria if I am not mistaken?
That would then not change the basic problem I am facing, but only make me swap the different INDEX MATCH combinations for a DGET

#### pjmorris

##### Well-known Member
Hi Paigan,

Having had another look at your problem I think the Eric's solution is probably all you need. I might change it to =VLOOKUP(\$A2,'Source Sheet'!\$A\$2:\$F\$18,\$A2+1,FALSE), but I suspect that that is exactly the same since False equates to 0. The point is that the VLOOKUP will find your criteria in Column A regardless of how that is sorted (provided it is Unique) and then use the same value to calculate which column to extract the data from. As a thought you might want to incorporate a second criteria to select the relevant column; without a second criteria you really only have two columns of data since the output is only dependent on one variable.

Hope this helps.

Regards

Peter

#### Paigan

##### New Member
Hello Peter,

first of all, thanks for your time.
Maybe I have not explained my problem sufficiently.
What I am trying to do is the following:
IF Cell A2=1 (For the real sheet A2 might be a text value such as abc)
THEN lookup value for another criterion xyz (which may reside in random column) in column 12 (may be a random column out of hundreds of columns) in Sourcesheet
ELSE
IF Cell A2=2 (For the real sheet A2 might be a text value such as asd)
THEN lookup value for another criterion zyx (which may reside in random column) in column 3 (again may be a random column, not necessarily sorted)

and so on and so forth...

Code:
``=IF(A2=abc;INDEX('Source Sheet'!\$Column12\$2:\$Column12\$18;(MATCH(xyz;'Source Sheet'!\$Random Column\$2:\$Random Column\$18;0)));IF(A2=asd;(INDEX('Source Sheet'!\$Column 3\$2:\$Column 3\$18;MATCH(zyx;'Source Sheet'!\$Random Column 2\$2:\$Random Column 2\$18;0)))...)))``
The formula Eric and you provided works perfectly for the given sheet.
As you see I cannot be sure what structure the final database will have and need to provide a solution that can be applied with out changing the order of the source sheet dataset and order.

In case I am missing your point I am sorry, but if I am correct the VLOOKUP formula will only work in the specific case I provided as an example, but not for the example I just gave.

Best

Paigan

#### pjmorris

##### Well-known Member
Paigan,

Can you provide some source data so we can seem exactly what your working with. It might be that you use VLOOKUP for criteria 1 and then use MATCH with the second criteria to determine which column your data should be drawn from.

Regards

#### Paigan

##### New Member
Peter,

I am sorry, but this is exactly the information I have.
I only know that the criteria A1 to A5 will be calculated and pulled in from another table.
This is literally all the information I have. I am trying to get the sheet at the moment.

Regards

Paigan