Multiple IF and SEARCH Forumla

**AustExcel** · 08-19-2009, 09:12 PM

Hi guys,

This is my first post and after reading the forum rules I believe this is the correct forum to post this in. I have also completed a detailed search of these forums and I'm still stuck on my problem. If anyone can help me out it would be greatly appreciated.

What I am trying to do is use a combination of IF and SEARCH forumlas to return a result based on a choice of constants available to the user.

Issue 1: IF Function to determine which worksheet to SEARCH
What I would like to add to this is if one of the 3rd criteria (Amount) is under a certain number then the results are derived from the 'Staff' worksheet'. (This is stage I have gotten to so far)

However, if the criteria is above a certain number then the results are derived from the 'Executive' worksheet. This is the part I'm so far unable to do.

Issue 2: SEARCH function returning the column per the user's selection on 'Matrix' page
In addition, criteria 2 (Type) can be selected which corresponds to a column in the Staff and Executive worksheets. So far I have been unable to get the correct search results to post in the Matrix page. What I mean by this is that currently it is set to Type1 only. I have tried using an IF function in cell G8 and include this in the formula, however, the forumla returns the contents of G8 in the cell instead.

Issue 3: SEARCH results are post in an accending order.
At this stage I have only been able to work how how to do this by using an AUto Filter on the Staff and Executive pages and sorting accending that way. Going forward as updates are made to the staff and executive pages I would prefer that this manual task was not required. Instead, the formula used returns the search in accending order.

I have attached a copy of the spreadsheet I'm working on at the last stage it was working as expected.

Any help much appreciated.

**martindwilson** · 08-19-2009, 10:21 PM

ok try this new sheet used (sheet1) for calculation you might have to adjust ranges a bit

**AustExcel** · 08-19-2009, 10:46 PM

Martin,

Thanks so much for your help and reply. I have tweaked the ranges a little after adding in some more data and also corrected the "Type3" error that came up (there was space between 'Type' and '3' on the Staff and Executive pages).

I'll continue to build on this. Are you aware of how to address issue 3 about getting the search results to automatically sort ascending?

Regards,

**martindwilson** · 08-19-2009, 11:18 PM

rank unique ascending then create another table using index match which you then use on matrix sheet ive done staff for you
and ive now just looked at clock and its 4:17 am i think perhaps i'd better go to bed lol