# Excel Formula help -

• 21st May 2013, 02:29 PM
basicchannel
Excel Formula help -
I've got a great report on Spiceworks that gives detailed information about memory in machines and how many banks are free etc. I've exported this to CSV but what I'd really like is a formula which goes through each entry and calculates how much it'd cost to upgrade according to Crucial.

 Name Model Memory Free Bank Upgrade Cost PC1 Optiplex 320 1024 0 PC2 Optiplex 360 2048 1

So what I want is a formula that will look at the model, then the memory then the free banks and if all three meet certain conditions it takes data from sheet 2, which contains the data from Crucial.

So for example If column B = "Optiplex 390" AND column C < 2048 AND column D = 0 THEN insert data from sheet2 Cell XX

I'm not expecting someone to present me a formula, but if you could point me in the right direction i'd really appreciate it.

Thanks
• 21st May 2013, 02:30 PM
sonofsanta
There is likely a more elegant way of doing it, but you could possibly do it with a series of tables and nested VLOOKUPs referencing them.
• 21st May 2013, 02:37 PM
tmcd35
Code:

`=IF(C2<2048, IF(D2>0, VLOOKUP(B2,Sheet2!A1:Z360,<Column XX>)),"No upgrade available")`
I think that's roughly what I'd do...
• 21st May 2013, 02:44 PM
basicchannel
Quote:

Originally Posted by tmcd35
Code:

`=IF(C2<2048, IF(D2>0, VLOOKUP(B2,Sheet2!A1:Z360,<Column XX>)),"No upgrade available")`
I think that's roughly what I'd do...

Ah sweet, that was actually very similar to what I had except I wasn't using the VLOOKUP thing. I'll give this a go
• 21st May 2013, 03:09 PM
pcstru
Often you find these nested if then structures are simple state space matrices. So you can add a column to sheet 1 which is just a string concatenation of the options "Optiplex32010241","Optiplex320102409" etc, and that compound key is a straight lookup into the solution matrix. No If's, Then's or buts.