Excel Formula help -

[basicchannel]

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.

 

[table=width: 500, class: grid]

[tr]

[td]Name[/td]

[td]Model[/td]

[td]Memory[/td]

[td]Free Bank[/td]

[td]Upgrade Cost[/td]

[/tr]

[tr]

[td]PC1[/td]

[td]Optiplex 320[/td]

[td]1024[/td]

[td]0[/td]

[td][/td]

[/tr]

[tr]

[td]PC2[/td]

[td]Optiplex 360[/td]

[td]2048[/td]

[td]1[/td]

[td][/td]

[/tr]

[/table]

 

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

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

[tmcd35]

=IF(C2<2048, IF(D2>0, VLOOKUP(B2,Sheet2!A1:Z360,)),"No upgrade available")

 

I think that's roughly what I'd do...

Edited May 21, 2013 by tmcd35

[basicchannel]

=IF(C2<2048, IF(D2>0, VLOOKUP(B2,Sheet2!A1:Z360,)),"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

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