Easier formula for nestled if statement

Paigan

New Member
Joined
Jun 10, 2014
Messages
12
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:
CriteriaLookup for Criteria
1nestled if statement
2nestled if statement
3nestled if statement
4nestled if statement
5nestled if statement

<tbody>
</tbody>
Sourcesheet:
uuuvvvwwwxxxyyyzzzaaa
1abcdeabc
2abcdebcd
3abcdecde
4abcdeabc
5abctraebcd
6abcdecde
7abcdeabc
8abcdebcd
9abcdecde
5abcdeabc
11abcdebcd
12abcdecde
13abasdfdeabc
1abcdebcd

<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
Joined
Dec 19, 2007
Messages
324
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
Joined
Nov 23, 2009
Messages
634
Office Version
2016
Platform
Windows
What is the expected results in the Lookup criteria column?
It looks like you could use Vlookup
 

Paigan

New Member
Joined
Jun 10, 2014
Messages
12
@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:
CriteriaLookup for Criteria (result)
1a
2b
3c
4d
5e

<tbody>
</tbody>
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,655
Office Version
2016
Platform
Windows
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
Joined
Jun 10, 2014
Messages
12
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
Joined
Aug 2, 2012
Messages
1,655
Office Version
2016
Platform
Windows
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
Joined
Jun 10, 2014
Messages
12
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
Joined
Aug 2, 2012
Messages
1,655
Office Version
2016
Platform
Windows
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
Joined
Jun 10, 2014
Messages
12
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
 

Forum statistics

Threads
1,086,240
Messages
5,388,646
Members
402,129
Latest member
saltoftheearth

Some videos you may like

This Week's Hot Topics

Top