Row wise vlookup with multiple range

[SAXON10]

HI,

How do vlookup row wise with multiple ranges one sheet to another sheet in excel.

Sheet-1 from Data;

ID

SUBJECT

TEXT

10

SC

A

AA

AB

AC

AD

AE

AF

AH

AI

AL

AM

AN

AO

AP

AQ

25

35

45

10

SC-1

B

A10

A20

A30

A40

A50

A60

A70

A80

A81

A82

A83

A84

A85

A86

A87

A88

A89

10

SC-1

C

Z21

Z22

Z23

Z24

Z25

Z26

Z27

Z28

Z29

Z30

Z31

Z32

Z33

Z34

20

SC

A

X5

X10

X15

X20

X30

X35

X40

X45

X50

X60

X70

X81

X82

X83

X84

X85

X86

20

SC-1

B

10

20

30

40

50

60

70

90

100

110

120

130

140

150

160

170

180

20

SC-1

C

200

210

220

226

230

240

250

260

270

280

290

300

320

330

<colgroup><col width="37" style="width:28pt"> <col width="65" style="width:49pt"> <col width="42" style="width:32pt"> <col width="37" style="width:28pt" span="17"> </colgroup><tbody>
</tbody>

Sheet 2 from Required Format;

ID

SUBJECT

10

SC

AA

AB

AC

AD

AE

AF

AH

AI

AL

AM

AN

AO

AP

AQ

25

35

45

20

SC

X5

X10

X15

X20

X30

X35

X40

X45

X50

X60

X70

X81

X82

X83

X84

X85

X86

<colgroup><col width="37" style="width:28pt"> <col width="65" style="width:49pt"> <col width="37" style="width:28pt" span="17"> </colgroup><tbody>
</tbody>

Please help me.

<colgroup><col width="37" style="width:28pt"> <col width="65" style="width:49pt"> <col width="42" style="width:32pt"> <col width="37" style="width:28pt" span="22"> </colgroup><tbody>
</tbody>

Sheet 2; Required Data;

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ID	SUBJECT	TEXT
2	10	SC	A	AA	AB	AC	AD	AE	AF	AH	AI	AL	AM	AN	AO	AP	AQ	25	35	45
3	10	SC-1	B	A10	A20	A30	A40	A50	A60	A70	A80	A81	A82	A83	A84	A85	A86	A87	A88	A89
4	10	SC-1	C	Z21	Z22	Z23	Z24	Z25	Z26	Z27	Z28	Z29	Z30	Z31	Z32	Z33	Z34
5	20	SC	A	X5	X10	X15	X20	X30	X35	X40	X45	X50	X60	X70	X81	X82	X83	X84	X85	X86
6	20	SC-1	B	10	20	30	40	50	60	70	90	100	110	120	130	140	150	160	170	180
7	20	SC-1	C	200	210	220	226	230	240	250	260	270	280	290	300	320	330
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ID	SUBJECT
2	10	SC	AA	AB	AC	AD	AE	AF	AH	AI	AL	AM	AN	AO	AP	AQ	25	35	45
3	20	SC	X5	X10	X15	X20	X30	X35	X40	X45	X50	X60	X70	X81	X82	X83	X84	X85	X86
Sheet2

Cell Formulas
Range	Formula
C2	{=VLOOKUP($A2&$B2,CHOOSE({1,2},Sheet1!$A$2:$A$7&Sheet1!$B$2:$B$7,Sheet1!D$2:D$7),2,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Taken from here:

The ultimate VLOOKUP trick – Multi-condition Lookup | Chandoo.org - Learn Microsoft Excel Online

WBD

 

[SAXON10]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ID	SUBJECT	TEXT
2	10	SC	A	AA	AB	AC	AD	AE	AF	AH	AI	AL	AM	AN	AO	AP	AQ	25	35	45
3	10	SC-1	B	A10	A20	A30	A40	A50	A60	A70	A80	A81	A82	A83	A84	A85	A86	A87	A88	A89
4	10	SC-1	C	Z21	Z22	Z23	Z24	Z25	Z26	Z27	Z28	Z29	Z30	Z31	Z32	Z33	Z34
5	20	SC	A	X5	X10	X15	X20	X30	X35	X40	X45	X50	X60	X70	X81	X82	X83	X84	X85	X86
6	20	SC-1	B	10	20	30	40	50	60	70	90	100	110	120	130	140	150	160	170	180
7	20	SC-1	C	200	210	220	226	230	240	250	260	270	280	290	300	320	330

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ID	SUBJECT
2	10	SC	AA	AB	AC	AD	AE	AF	AH	AI	AL	AM	AN	AO	AP	AQ	25	35	45
3	20	SC	X5	X10	X15	X20	X30	X35	X40	X45	X50	X60	X70	X81	X82	X83	X84	X85	X86

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula C2 {=VLOOKUP($A2&$B2,CHOOSE({1,2},Sheet1!$A$2:$A$7&Sheet1!$B$2:$B$7,Sheet1!D$2:D$7),2,0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Taken from here:

The ultimate VLOOKUP trick – Multi-condition Lookup | Chandoo.org - Learn Microsoft Excel Online

WBD

Thank you so much for your help, formula working fine. Thanks a lot.