Nested IF Functions

**Chemucla** · 09-26-2009, 06:32 PM

I am having trouble reducing the number of If functions in my formula. What I am trying to do is to be able to pull out a name from a database in a different excel file using two different codes. For example if Toys has two codes 1a and 1b and if I type either of those in my spreadsheet it will go to the database and display toys. Right now i am using if, index and match to accomplish this task.

**shg** · 09-26-2009, 06:55 PM

Welcome to the forum. Post an example workbook and explain in context.

**JBeaucaire** · 09-26-2009, 06:55 PM

Hi Chemucla, welcome to the forum.

If you'll mockup a sample workbook showing the dilemma and examples of your desired results, point out where the results came from if not obvious in your sample data, I'm sure we can be of help.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**Chemucla** · 10-06-2009, 11:18 AM

Sorry about the long wait I have been busy at work. Attached is an example of what I am trying to do. In the workbook sheet one contains a list of items and it can be referred to using two ids similary sheet two contains a list of colors which can also be referred to using two ids. On sheet three I have the formula to give be the item or color depending what i input into cell B4. Using either of the ids for each item or color I should get the correct name back, which I do. If i have four sheets with different items then their are too many nested if functions and excel has problems with my equation. Is there a simpler cleaner method of achieveing what I am trying to do.

Again thanks for your quick replys.

Thanks,

Carlos

**JBeaucaire** · 10-06-2009, 11:31 AM

You're problem appears to be expanding.

1) The data to search is actually in a separate workbook, isn't it? You stated that in your original post
2) The data is now starting to spread across multiple worksheets, from the post above?

INDEX/MATCH is the correct approach, but your data layout is not. I have to encourage you to either:

1) Organize all your data onto one sheet with the codes in one column and the resulting strings in another...one big set of data, or

2) Leave you current sheets, but create another sheet in that workbook that organizes all the data from the other sheets in the manner I have described in #1.

The main goal is to give your activity cell A4 one set of data to look at in the end.

**Chemucla** · 10-06-2009, 04:22 PM

Hi JBeaucaire,

You are correct the database is another file and the data in the database is in multiple worksheets. The problem is that we would like to keep it organized among different worksheets. So what you are recommending is that we combine all our products onto one sheet in that file to look? Is there no other way around the IF function nesting issue besides consolidating the entire database?

**JBeaucaire** · 10-06-2009, 04:27 PM

Option #2 has you maintaining your separate sheets for whatever reason that might be, AND having a method that creates a "searchable" single sheet out of those sheets. I've actually used ws_event macros to accomplish that whenever I've needed.

Then use that consolidated sheet when referring to the data remotely.