# To select from 4 options(cant use if)

#### zoharb

##### New Member
Respected,
I have to display answer based on the data in teh previous column(its in text-general , not number),
I cannot use if.
Zohar Batterywala
zoharsb@gmail.com

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Respected,
I have to display answer based on the data in teh previous column(its in text-general , not number),
I cannot use if.
Zohar Batterywala

[...]

=INDEX(\$B\$2:\$B\$100,MATCH(K2,\$F\$2:\$F\$100,0))

This matches K2 against F2:F100 and returns a corresponding value from B2:B100 as result.

On the basis of data in Column
E(Open)
Column F(high)
Column G(low)
Column H(close/Last)

Value in col V and Column W are calculated

They can be

In col V
(1)OH(Open high)
(2)OL(Open Low)

In col W
(1)HC(High Close)
(2)LC(Low Close)

On basis of that, the value in Col X has to be calculated

Case
(1)OHLC OR OLHC-difference of E(OPEN)-H(Close)
(2) Only OH (no LC or HC in Col V)
X has to be E-H , F-G
(3) Only OL (no LC or HC in Col V)
X has to be E-H , G-F

Now if there can be a case where in IF initially negating OHLC/OLHC,
I go to the second step,and then do "ONLY OL" STEP or "ONLY OH" step but that makes things complicated for debugging it at later time so I do not want to go in IF.
Zohar Batterywala
zoharsb@gmail.com

=INDEX(\$B\$2:\$B\$100,MATCH(K2,\$F\$2:\$F\$100,0))

This matches K2 against F2:F100 and returns a corresponding value from B2:B100 as result.

On the basis of data in Column
E(Open)
Column F(high)
Column G(low)
Column H(close/Last)

Value in col V and Column W are calculated

They can be

In col V
(1)OH(Open high)
(2)OL(Open Low)

In col W
(1)HC(High Close)
(2)LC(Low Close)

On basis of that, the value in Col X has to be calculated

Case
(1)OHLC OR OLHC-difference of E(OPEN)-H(Close)
(2) Only OH (no LC or HC in Col V)
X has to be E-H , F-G
(3) Only OL (no LC or HC in Col V)
X has to be E-H , G-F

Now if there can be a case where in IF initially negating OHLC/OLHC,
I go to the second step,and then do "ONLY OL" STEP or "ONLY OH" step but that makes things complicated for debugging it at later time so I do not want to go in IF.
Zohar Batterywala
zoharsb@gmail.com

Replies
1
Views
323
Replies
1
Views
214
Replies
9
Views
481
Replies
7
Views
215
Replies
0
Views
236

1,206,834
Messages
6,075,134
Members
446,123
Latest member
junkyardforme

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back