Office Software Thread, Excel Formula help - in Technical; I've got a great report on Spiceworks that gives detailed information about memory in machines and how many banks are ...
1. ## 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

2. 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.

3. 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...

4. ## Thanks to tmcd35 from:

basicchannel (21st May 2013)

5. 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

6. 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.

SHARE: