Particular Indirect and Match formula

R

richard1234567891011

Board Regular
Joined
Feb 21, 2018
Messages
91
Hi Guys,

I need help in order to change the indirect match formula, I have these data to Match.


[TABLE="width: 247"]
<tbody>[TR]
[TD="width: 160"][/TD]
[TD="class: xl65, width: 87, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Values[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Roma Sub 1[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]85000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 98 - 287[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]176700[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 59 - 204 - 266[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]240000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 127 - 258[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]178900[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 101-185-186[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]385000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 188[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]17900[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 124 - 261[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]178900[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 283 - 284[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]32200[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 36 - 233[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]207400[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 30 - 254 - 255[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]317000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 34 - 197 - 282[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]251800[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 94 - 257[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]170000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 90-275[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]191000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa Sub 58 - 202 - 203[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]332000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Paderno Sub 29 - 154[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]295000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Firenze Sub 28 - 42[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]145000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Firenze Sub 26 - 44[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]148000[/TD]
[/TR]
</tbody>[/TABLE]

In the other table, I have these:

[TABLE="width: 202"]
<tbody>[TR]
[TD="width: 115"][/TD]
[TD="class: xl65, width: 87, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Sub[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Roma[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]101[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]185[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]186[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]124[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]254[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]255[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]94[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]102[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]103[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]104[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]105[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]106[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]107[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]108[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]109[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]110[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]98[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Pisa[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]287[/TD]
[/TR]
</tbody>[/TABLE]


I have to take the values from the first table based on the values (Sub and City) of the second one.

Thank you guys,
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
What results do you expect to see in the first 3 rows of the second table? Would these be 85000, 385000 and 385000? And what result would you want to return for Pisa/110 in the 3rd to last row?
 
Upvote 0
Andrew Fergus said:
What results do you expect to see in the first 3 rows of the second table? Would these be 85000, 385000 and 385000? And what result would you want to return for Pisa/110 in the 3rd to last row?
Click to expand...

Thank you very much for your answer,

This is the expected result

So,the answer is yes,



[TABLE="width: 174"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]City
[/TD]
[TD="width: 64, bgcolor: transparent"]Sub
[/TD]
[TD="width: 104, bgcolor: transparent"]Expected result
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roma
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]85000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]101
[/TD]
[TD="bgcolor: transparent, align: right"]385000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]185
[/TD]
[TD="bgcolor: transparent, align: right"]385000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]186
[/TD]
[TD="bgcolor: transparent, align: right"]385000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]317000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]124
[/TD]
[TD="bgcolor: transparent, align: right"]178900
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]254
[/TD]
[TD="bgcolor: transparent, align: right"]317000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]255
[/TD]
[TD="bgcolor: transparent, align: right"]317000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]94
[/TD]
[TD="bgcolor: transparent, align: right"]170000
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]102
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]103
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]104
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]105
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]106
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]107
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]108
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]109
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]110
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]98
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pisa
[/TD]
[TD="bgcolor: transparent, align: right"]287
[/TD]
[TD="bgcolor: transparent, align: right"]176700


[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
something like this? (without zeroes)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]City[/td][td=bgcolor:#70AD47]Sub[/td][td=bgcolor:#70AD47]Values[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Roma[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
85000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]
30​
[/td][td]
317000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]
94​
[/td][td=bgcolor:#E2EFDA]
170000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]
98​
[/td][td]
176700​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]
101​
[/td][td=bgcolor:#E2EFDA]
385000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]
124​
[/td][td]
178900​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]
185​
[/td][td=bgcolor:#E2EFDA]
385000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]
186​
[/td][td]
385000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]
254​
[/td][td=bgcolor:#E2EFDA]
317000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]
255​
[/td][td]
317000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]
287​
[/td][td=bgcolor:#E2EFDA]
176700​
[/td][/tr]
[/table]
 
Upvote 0
sandy666 said:
something like this? (without zeroes)

[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]City[/COLOR]
[/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Sub[/COLOR]
[/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Values[/COLOR]
[/TD]
[/TR]
[TR]
[TD]Roma
[/TD]
[TD]
1​
[/TD]
[TD]
85000​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
30​
[/TD]
[TD]
317000​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
94​
[/TD]
[TD]
170000​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
98​
[/TD]
[TD]
176700​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
101​
[/TD]
[TD]
385000​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
124​
[/TD]
[TD]
178900​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
185​
[/TD]
[TD]
385000​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
186​
[/TD]
[TD]
385000​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
254​
[/TD]
[TD]
317000​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
255​
[/TD]
[TD]
317000​
[/TD]
[/TR]
[TR]
[TD]Pisa
[/TD]
[TD]
287​
[/TD]
[TD]
176700​
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

You changed the order and you removed the data, I cannot, the table is the second in the question, based on an indirect and match formula on the sub (of the second table), I have to take the data from the first table,

The issue is how to take the data considerig the multiple informations inside the cell.

Kind Regards
 
Last edited:
Upvote 0
don't quote whole post, use Reply instead of Reply With Quote

Hm, you can transform first table

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]City[/td][td=bgcolor:#70AD47]Sub[/td][td=bgcolor:#70AD47]Values[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Roma[/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]
85000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]98[/td][td]
176700​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]287[/td][td=bgcolor:#E2EFDA]
176700​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]59[/td][td]
240000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]204[/td][td=bgcolor:#E2EFDA]
240000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]266[/td][td]
240000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]127[/td][td=bgcolor:#E2EFDA]
178900​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]258[/td][td]
178900​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]101[/td][td=bgcolor:#E2EFDA]
385000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]185[/td][td]
385000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]186[/td][td=bgcolor:#E2EFDA]
385000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]188[/td][td]
17900​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]124[/td][td=bgcolor:#E2EFDA]
178900​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]261[/td][td]
178900​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]283[/td][td=bgcolor:#E2EFDA]
32200​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]284[/td][td]
32200​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]36[/td][td=bgcolor:#E2EFDA]
207400​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]233[/td][td]
207400​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]30[/td][td=bgcolor:#E2EFDA]
317000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]254[/td][td]
317000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]255[/td][td=bgcolor:#E2EFDA]
317000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]34[/td][td]
251800​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]197[/td][td=bgcolor:#E2EFDA]
251800​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]282[/td][td]
251800​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]94[/td][td=bgcolor:#E2EFDA]
170000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]257[/td][td]
170000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]90[/td][td=bgcolor:#E2EFDA]
191000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]275[/td][td]
191000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]58[/td][td=bgcolor:#E2EFDA]
332000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pisa[/td][td]202[/td][td]
332000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pisa[/td][td=bgcolor:#E2EFDA]203[/td][td=bgcolor:#E2EFDA]
332000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Paderno[/td][td]29[/td][td]
295000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Paderno[/td][td=bgcolor:#E2EFDA]154[/td][td=bgcolor:#E2EFDA]
295000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Firenze[/td][td]28[/td][td]
145000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Firenze[/td][td=bgcolor:#E2EFDA]42[/td][td=bgcolor:#E2EFDA]
145000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Firenze[/td][td]26[/td][td]
148000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Firenze[/td][td=bgcolor:#E2EFDA]44[/td][td=bgcolor:#E2EFDA]
148000​
[/td][/tr]
[/table]

is that what you want in first step?
 
Last edited:
Upvote 0
No, it is the table that I posted before as answer to
Andrew.
I need to extract the data, I don't need to transform the first table.
 
Last edited:
Upvote 0
I Need an Indirect Match as it is the title on a partial part of the text, this is the logic. First table are the data to take, cells in the second table are the referece cells in the match in my first answer the expected results.
 
Last edited:
Upvote 0
so take second table (post#1) and merge (whatever method) with the table (post#6) with the result: City/Sub/Value

edit:
table (post#6) is the same as first table (post#1) but detailed to one city one sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Index, match giving wrong results
Replies
3
Views
533
Fluff
Fluff
Jaafar Tribak
  • Question Question
Putting an Hyperlink in a MsgBox !!
2 3 4
Replies
31
Views
5K
kamranyd
K
G
  • Question Question
Fuzzy Match code
Replies
1
Views
2K
gelu
G
R
  • Locked
  • Question Question
Issue With an Important Formula
Replies
1
Views
382
jasonb75
J
Jaafar Tribak
  • Question Question
Cool Class for making Floating Shapes !
2
Replies
15
Views
5K
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,224,739
Messages
6,180,674
Members
452,993
Latest member
FDARYABEE

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