Row wise vlookup with multiple range

S

SAXON10

Board Regular
Joined
Jun 1, 2017
Messages
109
HI,

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

Sheet-1 from Data;

[TABLE="width: 773"]
<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>[TR]
[TD="class: xl70, width: 37"]ID[/TD]
[TD="class: xl70, width: 65"]SUBJECT[/TD]
[TD="class: xl70, width: 42"]TEXT[/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[/TR]
[TR]
[TD="class: xl70"]10[/TD]
[TD="class: xl70"]SC[/TD]
[TD="class: xl70"]A[/TD]
[TD="class: xl70"]AA[/TD]
[TD="class: xl70"]AB[/TD]
[TD="class: xl70"]AC[/TD]
[TD="class: xl70"]AD[/TD]
[TD="class: xl70"]AE[/TD]
[TD="class: xl70"]AF[/TD]
[TD="class: xl70"]AH[/TD]
[TD="class: xl70"]AI[/TD]
[TD="class: xl70"]AL[/TD]
[TD="class: xl70"]AM[/TD]
[TD="class: xl70"]AN[/TD]
[TD="class: xl70"]AO[/TD]
[TD="class: xl70"]AP[/TD]
[TD="class: xl70"]AQ[/TD]
[TD="class: xl70"]25[/TD]
[TD="class: xl70"]35[/TD]
[TD="class: xl70"]45[/TD]
[/TR]
[TR]
[TD="class: xl70"]10[/TD]
[TD="class: xl70"]SC-1[/TD]
[TD="class: xl70"]B[/TD]
[TD="class: xl70"]A10[/TD]
[TD="class: xl70"]A20[/TD]
[TD="class: xl70"]A30[/TD]
[TD="class: xl70"]A40[/TD]
[TD="class: xl70"]A50[/TD]
[TD="class: xl70"]A60[/TD]
[TD="class: xl70"]A70[/TD]
[TD="class: xl70"]A80[/TD]
[TD="class: xl70"]A81[/TD]
[TD="class: xl70"]A82[/TD]
[TD="class: xl70"]A83[/TD]
[TD="class: xl70"]A84[/TD]
[TD="class: xl70"]A85[/TD]
[TD="class: xl70"]A86[/TD]
[TD="class: xl70"]A87[/TD]
[TD="class: xl70"]A88[/TD]
[TD="class: xl70"]A89[/TD]
[/TR]
[TR]
[TD="class: xl70"]10[/TD]
[TD="class: xl70"]SC-1[/TD]
[TD="class: xl70"]C[/TD]
[TD="class: xl70"]Z21[/TD]
[TD="class: xl70"]Z22[/TD]
[TD="class: xl70"]Z23[/TD]
[TD="class: xl70"]Z24[/TD]
[TD="class: xl70"]Z25[/TD]
[TD="class: xl70"]Z26[/TD]
[TD="class: xl70"]Z27[/TD]
[TD="class: xl70"]Z28[/TD]
[TD="class: xl70"]Z29[/TD]
[TD="class: xl70"]Z30[/TD]
[TD="class: xl70"]Z31[/TD]
[TD="class: xl70"]Z32[/TD]
[TD="class: xl70"]Z33[/TD]
[TD="class: xl70"]Z34[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl70"]20[/TD]
[TD="class: xl70"]SC[/TD]
[TD="class: xl70"]A[/TD]
[TD="class: xl70"]X5[/TD]
[TD="class: xl70"]X10[/TD]
[TD="class: xl70"]X15[/TD]
[TD="class: xl70"]X20[/TD]
[TD="class: xl70"]X30[/TD]
[TD="class: xl70"]X35[/TD]
[TD="class: xl70"]X40[/TD]
[TD="class: xl70"]X45[/TD]
[TD="class: xl70"]X50[/TD]
[TD="class: xl70"]X60[/TD]
[TD="class: xl70"]X70[/TD]
[TD="class: xl70"]X81[/TD]
[TD="class: xl70"]X82[/TD]
[TD="class: xl70"]X83[/TD]
[TD="class: xl70"]X84[/TD]
[TD="class: xl70"]X85[/TD]
[TD="class: xl70"]X86[/TD]
[/TR]
[TR]
[TD="class: xl70"]20[/TD]
[TD="class: xl70"]SC-1[/TD]
[TD="class: xl70"]B[/TD]
[TD="class: xl70"]10[/TD]
[TD="class: xl70"]20[/TD]
[TD="class: xl70"]30[/TD]
[TD="class: xl70"]40[/TD]
[TD="class: xl70"]50[/TD]
[TD="class: xl70"]60[/TD]
[TD="class: xl70"]70[/TD]
[TD="class: xl70"]90[/TD]
[TD="class: xl70"]100[/TD]
[TD="class: xl70"]110[/TD]
[TD="class: xl70"]120[/TD]
[TD="class: xl70"]130[/TD]
[TD="class: xl70"]140[/TD]
[TD="class: xl70"]150[/TD]
[TD="class: xl70"]160[/TD]
[TD="class: xl70"]170[/TD]
[TD="class: xl70"]180[/TD]
[/TR]
[TR]
[TD="class: xl70"]20[/TD]
[TD="class: xl70"]SC-1[/TD]
[TD="class: xl70"]C[/TD]
[TD="class: xl70"]200[/TD]
[TD="class: xl70"]210[/TD]
[TD="class: xl70"]220[/TD]
[TD="class: xl70"]226[/TD]
[TD="class: xl70"]230[/TD]
[TD="class: xl70"]240[/TD]
[TD="class: xl70"]250[/TD]
[TD="class: xl70"]260[/TD]
[TD="class: xl70"]270[/TD]
[TD="class: xl70"]280[/TD]
[TD="class: xl70"]290[/TD]
[TD="class: xl70"]300[/TD]
[TD="class: xl70"]320[/TD]
[TD="class: xl70"]330[/TD]
[TD="class: xl70"]

[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]

[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2 from Required Format;

[TABLE="width: 731"]
<colgroup><col width="37" style="width:28pt"> <col width="65" style="width:49pt"> <col width="37" style="width:28pt" span="17"> </colgroup><tbody>[TR]
[TD="class: xl70, width: 37"]ID[/TD]
[TD="class: xl70, width: 65"]SUBJECT[/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[/TR]
[TR]
[TD="class: xl70"]10[/TD]
[TD="class: xl70"]SC[/TD]
[TD="class: xl70"]AA[/TD]
[TD="class: xl70"]AB[/TD]
[TD="class: xl70"]AC[/TD]
[TD="class: xl70"]AD[/TD]
[TD="class: xl70"]AE[/TD]
[TD="class: xl70"]AF[/TD]
[TD="class: xl70"]AH[/TD]
[TD="class: xl70"]AI[/TD]
[TD="class: xl70"]AL[/TD]
[TD="class: xl70"]AM[/TD]
[TD="class: xl70"]AN[/TD]
[TD="class: xl70"]AO[/TD]
[TD="class: xl70"]AP[/TD]
[TD="class: xl70"]AQ[/TD]
[TD="class: xl70"]25[/TD]
[TD="class: xl70"]35[/TD]
[TD="class: xl70"]45[/TD]
[/TR]
[TR]
[TD="class: xl70"]20[/TD]
[TD="class: xl70"]SC[/TD]
[TD="class: xl70"]X5[/TD]
[TD="class: xl70"]X10[/TD]
[TD="class: xl70"]X15[/TD]
[TD="class: xl70"]X20[/TD]
[TD="class: xl70"]X30[/TD]
[TD="class: xl70"]X35[/TD]
[TD="class: xl70"]X40[/TD]
[TD="class: xl70"]X45[/TD]
[TD="class: xl70"]X50[/TD]
[TD="class: xl70"]X60[/TD]
[TD="class: xl70"]X70[/TD]
[TD="class: xl70"]X81[/TD]
[TD="class: xl70"]X82[/TD]
[TD="class: xl70"]X83[/TD]
[TD="class: xl70"]X84[/TD]
[TD="class: xl70"]X85[/TD]
[TD="class: xl70"]X86[/TD]
[/TR]
</tbody>[/TABLE]

Please help me.
[TABLE="width: 958"]
<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>[TR]
[TD="class: xl70, width: 37"][/TD]
[TD="class: xl70, width: 65"][/TD]
[TD="class: xl70, width: 42"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[TD="class: xl67, width: 37"][/TD]
[/TR]
[TR]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2; Required Data;
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes

Book1
ABCDEFGHIJKLMNOPQRST
1IDSUBJECTTEXT
210SCAAAABACADAEAFAHAIALAMANAOAPAQ253545
310SC-1BA10A20A30A40A50A60A70A80A81A82A83A84A85A86A87A88A89
410SC-1CZ21Z22Z23Z24Z25Z26Z27Z28Z29Z30Z31Z32Z33Z34
520SCAX5X10X15X20X30X35X40X45X50X60X70X81X82X83X84X85X86
620SC-1B1020304050607090100110120130140150160170180
720SC-1C200210220226230240250260270280290300320330
Sheet1



Book1
ABCDEFGHIJKLMNOPQRS
1IDSUBJECT
210SCAAABACADAEAFAHAIALAMANAOAPAQ253545
320SCX5X10X15X20X30X35X40X45X50X60X70X81X82X83X84X85X86
Sheet2
Cell Formulas
RangeFormula
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
 
Upvote 0
wideboydixon said:
ABCDEFGHIJKLMNOPQRST
ID SUBJECT TEXT
10SCA AA AB AC AD AE AF AH AI AL AM AN AO AP AQ 253545
10SC-1B A10 A20 A30 A40 A50 A60 A70 A80 A81 A82 A83 A84 A85 A86 A87 A88 A89
10SC-1C Z21 Z22 Z23 Z24 Z25 Z26 Z27 Z28 Z29 Z30 Z31 Z32 Z33 Z34
20SCA X5 X10 X15 X20 X30 X35 X40 X45 X50 X60 X70 X81 X82 X83 X84 X85 X86
20SC-1B 1020304050607090100110120130140150160170180
20SC-1C 200210220226230240250260270280290300320330

<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>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>
Sheet1​



ABCDEFGHIJKLMNOPQRS
IDSUBJECT
SCAA AB AC AD AE AF AH AI AL AM AN AO AP AQ
SCX5 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>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]20[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=VLOOKUP($A2&$B2,CHOOSE({1,2},Sheet1!$A$2:$A$7&Sheet1!$B$2:$B$7,Sheet1!D$2:D$7),2,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Taken from here:

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

WBD
Click to expand...



Thank you so much for your help, formula working fine. Thanks a lot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
If & And Condition based on lookup value change at every 30 row
Replies
2
Views
405
mba_110
M
R
  • Question Question
2 Different SUMS for 2 columns with duplicates
Replies
3
Views
249
Fluff
Fluff
D
  • Question Question
Reorder with swapping rows
Replies
2
Views
635
Dipchikov
D
M
  • Question Question
Excel Formula Help - need excel to look up a text and sum all values that have that text in multiple rows and columns
Replies
2
Views
895
Marcelo Branco
M
T
  • Question Question
incrementing and checking till getting value less than specified.
Replies
0
Views
748
trustmeasfrnd
T

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top