Multiple HLOOKUP in IF formula

[KopiSusu]

Hi there,
I need your help to build a IF formula with multiple HLOOKUP in it.
Thank you in advance.

Formula rules:

This column is in Row-2	Result Color showed in Col-B	Result Vol showed in Col-C
x < -299	WHITE	30
-300 < x < -100	WHITE	20
-101 < x < 1	BLACK	10
0 < x < 101	WHITE	10
100 < x < 300	BLACK	20
x > 299	BLACK	30

The Simulation Spreadsheet

DAY-1

DAY-2

DAY-3

DAY-4

DAY-5

DAY-6

DAY-7

DAY-8

DAY-9

DAY-10

DAY-11

DAY-12

DAY-13

X -->

-78.1

-399.5

-413.6

-226.5

197.6

576

496.4

651.3

658.9

1308.9

1246.9

857.7

Result Col

Result Vol

DAY-2

BLACK

10

DAY-3

WHITE

30

DAY-4

WHITE

30

DAY-5

WHITE

20

DAY-6

BLACK

20

DAY-7

BLACK

30

DAY-8

BLACK

30

DAY-9

BLACK

30

DAY-10

BLACK

30

DAY-11

BLACK

30

DAY-12

BLACK

30

DAY-13

BLACK

30

 

[Bosquedeguate]

Create a table like following example
Cols A,B,C
-99999, -300, White, 30
-299,-101,White,20
-100,0,Black,10
1,100,White,10
101,299,Black,20
300,9999999,Black,20

Name the table: TableBWVol

Each row of output is, e.g
Col A,B,C

DAY 2,=VLOOKUP(D$2,TableBWVol,2,true),VOOKUP(D$2,TableBWVol,3,true)

DAY 3,=VLOOKUP(E$2,TableBWVol,2,true),VLOOKUP(E$2,TableBWVol,3,true)

 

[Bosquedeguate]

How about this solution?
VLOOKUP Table (TableBWVol)

GetLastRow.xlsm
	B	C	D	E
45	-999999	-300	WHITE	30
46	-299	-100	WHITE	20
47	-101	0	BLACK	10
48	1	100	WHITE	10
49	101	300	BLACK	20
50	301	999999	BLACK	30
Sheet1

Daily HLOOKUP Table (DailyX - table name)
Columns labeled DAY2 - DAY3 are the DailyX Horizontal table

GetLastRow.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
17	DAY-1			DAY-2	DAY-3	DAY-4	DAY-5	DAY-6	DAY-7	DAY-8	DAY-9	DAY-10	DAY-11	DAY-12	DAY-13
18			X -->	-78.1	-399.5	-413.6	-226.5	197.6	576	496.4	651.3	658.9	1308.9	1246.9	857.7
Sheet2

Cell Formulas
Range	Formula
A17	A17	="DAY-"&1
D17:O17	D17	=LEFT($A$17,4)&COLUMN(A17)+1

Named Ranges
Name	Refers To	Cells
DailyX	=Sheet2!$D$17:$O$18	G17

Daily Color and Volume List Using HLOOKUP and VLOOKUP

GetLastRow.xlsm
	A	B	C
17	DAY-1
18			X -->
19		Result Col	Result Vol
20	DAY-2	BLACK	10
21	DAY-3	WHITE	30
22	DAY-4	WHITE	30
23	DAY-5	WHITE	20
24	DAY-6	BLACK	20
25	DAY-7	BLACK	30
26	DAY-8	BLACK	30
27	DAY-9	BLACK	30
28	DAY-10	BLACK	30
29	DAY-11	BLACK	30
30	DAY-12	BLACK	30
31	DAY-13	BLACK	30
Sheet2

Cell Formulas
Range	Formula
A17	A17	="DAY-"&1
A20:A31	A20	=LEFT($A$17,4)&ROW(A20)-ROW($A$17)-1
B20:B31	B20	=VLOOKUP(HLOOKUP(A20,DailyX,2,FALSE),TableBWVol,3,TRUE)
C20:C31	C20	=VLOOKUP(HLOOKUP(A20,DailyX,2,FALSE),TableBWVol,4,TRUE)

Named Ranges
Name	Refers To	Cells
DailyX	=Sheet2!$D$17:$O$18	B20:C31
TableBWVol	=Sheet1!$B$45:$E$50	B20:C31

Please Note: The Labels DAY-2 to DAY-13 are calculated labels using the ROW number and COLUMN number of the DAY-1 cell.
This easily facilitates copying the Results Sheet.


Please let me know if you have questions on this implementation

 

[KopiSusu]

Thank you for your response
I'm still driving on my home.
Will let you know after I get a chance open my computer.
Once again, thanks!

 

[Bosquedeguate]

Hi
Did the example that was sent to you work?

Please set the status of the Post to Solved when you have a working solution