I have a speadsheet with hundreds of row, a sample file is attached.
I require a formaula that does a lookup, does a word substitution and matches it to a value. The formulas are required for columns F, H and J.
The resulting value of formula comes from the preceeding column (E, G and I)
The formula should lookup the "VM Name" that is prefixed with the word "windows-" and take the value in column E where the "VM Name" is prefixed with the word "linux-"
For example F2 should have the value of E16, H2 should have the value of G16, and J2 should have the value of I16, because its source column A2 value is "windows-ds5v2" and if we lookup its linux equivilent in "VM Name" we find "linux-ds5v2" that has a cost value of "1.405", "0.63333", and "0.42698" respectively, in columns E, G and I.
I have provided two worksheets, 1 that requires the formaula, and 1 that shows what the final output should look like when a formaula is used.
Please note, that the should be no values retruned to columns F, H and J where "VM Name" is prefixed with the word "linux-"
Bookmarks