If, and, or, index, & match

**mattpriest08** · 04-27-2010, 02:46 PM

Can anybody tell me how I would re-write the formula below using either ANDs, IFs, or OR equations?

My problem is this formula is resulting in an N/A result and I need it to display a zero if any of the criteria are missing. The way the spreadsheet is set up is a year in Column D, a type of project in Column E, and a region in Column G.

I have tried to fix this with an IF statement that says if Column E is "", insert 0 but it says there is an error with the formula being too long or the formula not being valid.

=(IF($D48=2010,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0)),12),INDEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),12)),(IF($D48=2011,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),11),INDEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),11)),(IF($D48=2012,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),10),INDEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),10)),(IF($D48=2013,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),9),INDEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),9)),(IF($D48=2014,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),8),INDEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),8)),0)))))))))

Thanks for your help

**NBVC** · 04-27-2010, 02:58 PM

Does this shorter version work?

=IF($Data!$E48="",0,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),LOOKUP(RIGHT($D48,2)+0,{10,11,12,13,14},{12,11,10,9,8})),INDEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),LOOKUP(RIGHT($D48,2)+0,{10,11,12,13,14},{12,11,10,9,8}))))

**mattpriest08** · 04-27-2010, 03:04 PM

Yes that is amazing.

Can you explain to me how that works?

**NBVC** · 04-27-2010, 03:11 PM

it first check E48 and if blank returns 0, else it continues with formula...

Then checks if G48 is "NA", if it is it completes the first INDEX function, else it completes the second INDEX function.

The Index/Match functions are basically the same as yours, but I used the last 2 digits of D48 to determine which column to go to...achieved with the LOOKUP() function.. which looks up a value in an array that is listed in ascending order (the first array) and finds the last value that is smaller than or equal to the search value and extracts the corresponding item from the second array at the same point.

**mattpriest08** · 04-27-2010, 03:24 PM

I'm sorry but I'm a not tracking. Can you bring that down one level for me?

**NBVC** · 04-27-2010, 03:38 PM

I am guessing you are talking about the LOOKUP() function and how it is used to determine the column number?

For example in this part of your original formula:

IF($D48=2010,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),12),IN DEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),12))

After you checked for D48 = 2010, you chech G48 for existance of "NA", you determine which of the 2 INDEX() functions to perform, correct. I.e. =IF(logical_test,value_if_true,value_if_false).

and you continue to do same check for each possible year in D48.

the only difference in each of these checks is that if the year is different, then the column number to do the Index/Match function is different.

e.g. for 2010, you have column 12, for 2011, you have 11, for 2012, you have 10, etc..

so I took advantage of that pattern to shorten the formula and performed a LOOKUP() that looks at the last 2 digits of the value in D48 (I could have also just used the whole 4-digit number).... so in D28 is 2010, the lookup value in the LOOKUP function is "10"... I added +0 to convert it to a real number. Then I look that number up in the first array {10,11,12,13,14}.. when I find it, I extract from the second array at the same position that I found the match... {12,11,10,9,8}... so here the 12 get pulled and that is used in the column Reference of the INDEX() function. All this reduces the number of times you have to repeat the same function over and over, just changing the one argument (column index).

I hope that is better explained.

**mattpriest08** · 04-27-2010, 03:55 PM

Yes I'm tracking the logic, now here is a next step. Dragging this formula out to my various resulting Y1, Y2, etc, I simply update the second vector.

The way it works is I've got a launch year for a new product i.e. 2010 and I have corresponding Net Sales Values for 2010 - 2015 respectively for each of the six years. If I insert a year such as 2013 that does not have results for years 2010-2012 it is now showing me an N/A for those years.

How would I fix that? Below is the formula as I have extrapolated it.

=IF($E48="",0,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),LOOKUP(RIGHT($D48,2)+0,{10,11,12,13,14},{10,9,8})),INDEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),LOOKUP(RIGHT($D48,2)+0,{10,11,12,13,14},{10,9,8}))))

**NBVC** · 04-27-2010, 04:09 PM

Sorry... why did you change the second array to just {10,9,8}... don't get it

where is there no result for 2013?

**mattpriest08** · 04-27-2010, 04:13 PM

I carried the formula left two columns.

The Launch Year is 2013 for example. Therefore there would be six columns (they are H through M on my sheet) labeled NSV 2010, 2011 and so forth.

If the launch year is 2013, obviously there would be no sales data in 2010-2012; leaving the first three years blank. When I extrapolated that formula out (possibly incorrect) it worked fine if there is a corresponding value in the table but when there is no answer, it still gives an N/A.

Maybe it would help if I attached the spreadsheet?

**mattpriest08** · 04-27-2010, 04:29 PM

Nevermind, I fixed the vector formula part. Now I'm just trying to figure out how to get rid of the N/A if the formula does not have a result. Such as the second part of the equation. I've got it to where it will give me the correct result each and every time but now I am working to get the N/A off again.

Suggestions?

**NBVC** · 04-27-2010, 04:39 PM

Perhaps

=IF(Data!$E48="",0,IF($G48="NA",IF(ISNUMBER(MATCH(Data!$E48,Profiles!$B$5:$B$10,0)),INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),LOOKUP(RIGHT($D48,2)+0,{10,11,12,13,14},{12,11,10,9,8})),""),IF(ISNUMBER(MATCH(Data!$E48,Profiles!$B$15:$B$19,0)),INDEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),LOOKUP(RIGHT($D48,2)+0,{10,11,12,13,14},{12,11,10,9,8})),"")))

you may have to adjust ranges again to suit

**mattpriest08** · 04-27-2010, 04:57 PM

Still N/A. I'm playing with the formula a bit to see if I put it in wrong. Please send any other suggestions.

**NBVC** · 04-27-2010, 05:00 PM

The latest formula checks if there is a match to E48 in Profiles!$B$5:$B$10 or Profiles!$B$15:$B$19, depending on if G48 had "NA" or not....

If there is not a match, you should get a blank returned, otherwise it will proceed to give you the indexed result.

**mattpriest08** · 04-27-2010, 05:10 PM

The problem is there is a match, just not for that particular column it is asking for I guess.

I'm not really sure at this point without you looking at the sheet.

**NBVC** · 04-27-2010, 09:11 PM

Attach the sheet here (void of confidential data)

If, and, or, index, & match

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Re: If, and, or, index, & match

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions