Auto fill cells getting value from different worksheet but only if conditions met

I have a spreadsheet formatted like the below...

id	name	options	increment	value
50	name A	options 12	1
50	name A	options 64	2
50	name A	options 12	3
51	name B	options 12	1
51	name B	options 64	2
51	name B	options 12	3
51	name B	options 64	4
52	name C	options 64	1
52	name C	options 12	2

I wish to fill values into the value column. The values to fill are in another worksheet but I need to fill based on multiple conditions.

The worksheet containing the required values is just simply like this in column A:-

Option1

Option2

Option3

Option4

Option5

Option6

Option7

Option8

The value column will check the value in the increment column and fill the cell based on it's corresponding value in the other worksheet. This bit can be simply fetched with the following formula in the value column...

Please Login or Register  to view this content.

And then copy and drag this down the column.

But I only want the value column to get filled based on conditions met from the options column. For example:-

id	name	options	increment	value
50	name A	options 12	1	option 1
50	name A	options 64	2
50	name A	options 12	3	option 1
51	name B	options 12	1	option 1
51	name B	options 64	2
51	name B	options 12	3	option 1
51	name B	options 64	4	option 1
52	name C	options 64	1
52	name C	options 12	2	option 1

Notice how it only fills value from other worksheet if the options contains 'options 12' and not other values such as 'options 64'.

So I presume the formula

Please Login or Register  to view this content.

requires some extending to say skip if options column equals valueX, valueY or valueZ.

Please let me know if you would like me to supply any further information. Thanks in advance.

Okay, please find attached file.

In the 'Options' worksheet, you'll see empty column (I).

Based on the value in column H, I wish to get the corresponding option from the 'Sheet2' worksheet.

For example:-

If column H is 1, get value from Sheet2!A1.
If column H is 6, get value from Sheet2!A6.

(Top row in Sheet2 needs removing).

But only fill column I if column F doesn't contain "Square" or "Panorama".

Hope that helps. Thanks.

Oh wow, that is brilliant. Thank you so much...

More to come on this one (this spreadsheet is becoming a nightmare but am getting there now).

Ah great, thanks for the suggestion.

I only saved as .xlsm because I couldn't figure out how to save as .xls with macros within the workbook. If I save as .xlsb in future, will any macros still run within it?

Thanks.

One other query based on this formula...

Formula:

Please Login or Register  to view this content.

When copying this down multiple rows, how would I modify it so that if condition is met, leave the cell value intact (rather than making it blank). Is that possible?

Perfect, thanks very much.