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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
What is the expected results in the Lookup criteria column?
It looks like you could use Vlookup
 
Upvote 0
@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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top