help with Excel formula - simplfy nested if statements

i have an excel formula in Excel 2007 that works, but is very ugly. I am sure that there is a better way to do this. What I am trying to do is produce a count of the amount of cores in my servers. I have the count of processors and their corresponding names, a list of the processors with their corresponding core count.

I need to do a search of the processor name which looks something like this:

Intel Xeon E5640 2.67GHz


And then reference it against the table that has the processor name and core count, which looks like this

5640                                       6

So basically I want it to search for the processor name (ie, 5640, 5660, 5670......) and return the corresponding value from another table. Right now I am using a very long nested if statement, but it will get longer and longer as more processor models are added and there has got to be a better way. Here is the if statement:

=IF(ISNUMBER(SEARCH("3430",H17)),4,(IF(ISNUMBER(SEARCH("5120",H17)),2,(IF(ISNUMBER(SEARCH("5160",H17)),2,(IF(ISNUMBER(SEARCH("5506",H17)),4,(IF(ISNUMBER(SEARCH("5560",H17)),4,(IF(ISNUMBER(SEARCH("5620",H17)),4,(IF(ISNUMBER(SEARCH("5640",H17)),6,(IF(ISNUMBER(SEARCH("5650",H17)),6,(IF(ISNUMBER(SEARCH("5670",H17)),6,(IF(ISNUMBER(SEARCH("7420",H17)),4,(IF(ISNUMBER(SEARCH("7460",H17)),6,1)))))))))))))

LVL 12
ryan80Asked:
Who is Participating?
 
barry houdiniCommented:
If your table is in Y2:Z20 then try this formula

=LOOKUP(2^15,SEARCH(Y$2:Y$20,H17),Z$2:Z$20)

regards, barry
0
 
KnutsonBMCommented:
could you post your sheet, that could make this a lot easier......

thanks,

Brandon
0
 
byundtCommented:
Note that Barry's elegant formula requires that every cell in Y2:Y20 contain a value. If one or more is blank, the formula will erroneously return a value from column Z on the same row as the last blank cell.

Brad
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ryan80Author Commented:
here is the excel file.

the whole picture is that Server 2003 reports the core count. 2008 reports the processor count. So i need to go through and when the operating system is 2008, multiply the proccesor count times the amount of cores for that model, which a list of is contained in the second sheet.

Hope that helps

@barryhoudini

the problem that I have, is that the value range and the result range are the ones that are the same. The lookup_range is not the same as the result_range. It needs to look for a value, and return a specific number if it finds that value.
server-inventory-core-count-temp.xlsx
0
 
gowflowCommented:
Would VBA be an option for you ?
gowflow
0
 
byundtCommented:
Try the following modification of Barry's formula in cell K7, then copy it down:
=IF(ISNA(LOOKUP(2^15,SEARCH(Sheet2!A$1:A$11,E7),Sheet2!B$1:B$11)),1,LOOKUP(2^15,SEARCH(Sheet2!A$1:A$11,E7),Sheet2!B$1:B$11))
0
 
byundtCommented:
I changed your lookup table by adding a blank cell in A1 and the number 1 in B1. You can now use Barry's original formula:
=LOOKUP(2^15,SEARCH(Sheet2!A$1:A$12,E7),Sheet2!B$1:B$12)
0
 
byundtCommented:
Your file with Barry's formula
server-inventory-core-count-temp.xlsx
0
 
ryan80Author Commented:
thanks, that looks a lot nicer than what I had. Could you help me understand a few things though?

The SEARCH function, what does it return? When I copy that formula out to another cell, it does not work. From what it looks like, it will return the cell that contains the value that is found? do the $ before the cell #'s signify this?

Also why is 2^15 used in the LOOKUP function?
0
 
byundtCommented:
SEARCH returns an array of #VALUE! error values and string starting positions. In this case, the first result in the array (searching for nothing) returns the starting position of 1. And the exact match for the four digit number returns a larger starting position. We need to search this array and return a value from the same row in column Z.

The reason that SEARCH isn't working in a separate cell is that it returns an array of values, one for each row in the lookup table.

If it cannot find an exact match and there is nothing bigger in its second parameter, the LOOKUP function has the useful property of returning the last value that it found of the same data type. The number 2^15 is bigger than any possible match, so you get a value from column Z in the same row as the last matching value from column Y.

The $ before the row numbers just mean that they should remain fixed when you copy the formula down. Row numbers without the $ automatically increase by 1 with each successive row in the copy--so the formula is always searching column E in the same row as the formula.

You may find it helpful to use the Formulas...Evaluate Formula menu item. Click the Evaluate button several times to watch how Excel interprets the formula.
0
 
byundtCommented:
ryan80,
Do remember that it is Barry's formula. I'm just 'splaining how it works.

Brad
0
 
ussc1Commented:
Is this solution meeting the need?  I would use INDEX so the table doesn't need to be sorted and so it will skip blanks.  
0
 
byundtCommented:
ussc1,
The table doesn't need to be sorted. LOOKUP is trying to find a value that is bigger than any possible return from SEARCH, so sorting is no longer an issue.

The only requirement is that the blank cell be the first row in the table, and that there be no more blank cells.

Brad
0
 
ryan80Author Commented:
Thanks,

The formula works great, and that explanation really helps me understand what is going on.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.