Matching Values in Tables

B

BeppeBM

New Member
Joined
Jun 11, 2019
Messages
2
[FONT=&quot]I have a MAIN empty table (for example 6 rows and 6 columns, see table below) and a SMALL table which is a subset of the main one (say 2 rows and 3 columns) and contains data. What formula I can use in the MAIN table so that each cell gets populated with the corresponding values from the SMALL table, living blanks all the cells which don't exist in the SMALL one (in the example below cell named "Yellow-Mon" in the MAIN table will be left blank since it doesn't exist in the SMALL table, while cell "Green-Sat" will be populated with the number 0.6 from the corresponding cell in the SMALL table). Thanks[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]MAIN Table[/FONT]
[TABLE="class: rz6fp9-19 dGjtNg"]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TH="class: rz6fp9-25 lnwKgG, align: center"][/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Mon[/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Tue[/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Wed[/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Thu[/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Fri[/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Sat[/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: rz6fp9-21 hzNEby"]Yellow[/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[/TR]
[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: rz6fp9-21 hzNEby"]Red[/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[/TR]
[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: rz6fp9-21 hzNEby"]Blue[/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[/TR]
[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: rz6fp9-21 hzNEby"]Green[/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[/TR]
[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: rz6fp9-21 hzNEby"]Pink[/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[/TR]
[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: rz6fp9-21 hzNEby"]White[/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[TD="class: rz6fp9-21 hzNEby"][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]
[/FONT]
[FONT=&quot]SMALL Table[/FONT]
[FONT=&quot]
[/FONT]
[TABLE="class: rz6fp9-19 dGjtNg"]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TH="class: rz6fp9-25 lnwKgG, align: center"][/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Tue[/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Fri[/TH]
[TH="class: rz6fp9-25 lnwKgG, align: center"]Sat[/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: rz6fp9-21 hzNEby"]Red[/TD]
[TD="class: rz6fp9-21 hzNEby"]0.5[/TD]
[TD="class: rz6fp9-21 hzNEby"]0.9[/TD]
[TD="class: rz6fp9-21 hzNEby"]0.77[/TD]
[/TR]
[TR="class: rz6fp9-20 jqNRVW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: rz6fp9-21 hzNEby"]Green[/TD]
[TD="class: rz6fp9-21 hzNEby"]0.3[/TD]
[TD="class: rz6fp9-21 hzNEby"]0.45[/TD]
[TD="class: rz6fp9-21 hzNEby"]0.6[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Color[/td][td=bgcolor:#5B9BD5]Mon[/td][td=bgcolor:#5B9BD5]Tue[/td][td=bgcolor:#5B9BD5]Wed[/td][td=bgcolor:#5B9BD5]Thu[/td][td=bgcolor:#5B9BD5]Fri[/td][td=bgcolor:#5B9BD5]Sat[/td][td][/td][td=bgcolor:#5B9BD5]Color[/td][td=bgcolor:#5B9BD5]Tue[/td][td=bgcolor:#5B9BD5]Fri[/td][td=bgcolor:#5B9BD5]Sat[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Yellow[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#DDEBF7]Red[/td][td=bgcolor:#DDEBF7]
0.5​
[/td][td=bgcolor:#DDEBF7]
0.9​
[/td][td=bgcolor:#DDEBF7]
0.77​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Green[/td][td]
0.3​
[/td][td]
0.45​
[/td][td]
0.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Blue[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Green[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Pink[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]White[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Color[/td][td=bgcolor:#70AD47]Mon[/td][td=bgcolor:#70AD47]Tue[/td][td=bgcolor:#70AD47]Wed[/td][td=bgcolor:#70AD47]Thu[/td][td=bgcolor:#70AD47]Fri[/td][td=bgcolor:#70AD47]Sat[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Blue[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Green[/td][td][/td][td]
0.3​
[/td][td][/td][td][/td][td]
0.45​
[/td][td]
0.6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pink[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Red[/td][td][/td][td]
0.5​
[/td][td][/td][td][/td][td]
0.9​
[/td][td]
0.77​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]White[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Yellow[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]// Append1
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace1 = Table.ReplaceValue(Source1,null,"x",Replacer.ReplaceValue,{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}),
    UnPivot1 = Table.UnpivotOtherColumns(Replace1, {"Color"}, "Attribute", "Value"),
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    UnPivot2 = Table.UnpivotOtherColumns(Source2, {"Color"}, "Attribute", "Value"),
    Source = Table.Combine({UnPivot1, UnPivot2}),
    Replace = Table.ReplaceValue(Source,"x",null,Replacer.ReplaceValue,{"Value"}),
    Pivot = Table.Pivot(Replace, List.Distinct(Replace[Attribute]), "Attribute", "Value", List.Sum)
in
    Pivot[/SIZE]
 
Upvote 0
You'll have to extend your match ranges as your dataset gets bigger


Book1
ABCDEFG
1MAIN Table
2MonTueWedThuFriSat
3Yellow      
4Red0.50.90.77
5Blue
6Green0.30.450.6
7Pink
8White
9
10
11
12
13
14SMALL Table
15TueFriSat
16Red0.50.90.77
17Green0.30.450.6
Sheet1
Cell Formulas
RangeFormula
B3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(B$2,$B$15:$D$15,0)),"")
C3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(C$2,$B$15:$D$15,0)),"")
D3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(D$2,$B$15:$D$15,0)),"")
E3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(E$2,$B$15:$D$15,0)),"")
F3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(F$2,$B$15:$D$15,0)),"")
G3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(G$2,$B$15:$D$15,0)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
574
smozgur
smozgur
kelly mort
  • Question Question
Trying to use "PageSetup.PaperSize" with a constant not working
Replies
6
Views
2K
kelly mort
kelly mort
A
  • Question Question
Conditional formatting is not working for less than TODAY(), only for greater than
Replies
2
Views
1K
alexpazolini
A
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,223,950
Messages
6,175,582
Members
452,653
Latest member
craigje92

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