Particular Indirect and Match formula

[richard1234567891011]

Hi Guys,

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

	Values
Roma Sub 1	85000
Pisa Sub 98 - 287	176700
Pisa Sub 59 - 204 - 266	240000
Pisa Sub 127 - 258	178900
Pisa Sub 101-185-186	385000
Pisa Sub 188	17900
Pisa Sub 124 - 261	178900
Pisa Sub 283 - 284	32200
Pisa Sub 36 - 233	207400
Pisa Sub 30 - 254 - 255	317000
Pisa Sub 34 - 197 - 282	251800
Pisa Sub 94 - 257	170000
Pisa Sub 90-275	191000
Pisa Sub 58 - 202 - 203	332000
Paderno Sub 29 - 154	295000
Firenze Sub 28 - 42	145000
Firenze Sub 26 - 44	148000

<tbody>
</tbody>

In the other table, I have these:

	Sub
Roma	1
Pisa	101
Pisa	185
Pisa	186
Pisa	30
Pisa	20
Pisa	10
Pisa	124
Pisa	254
Pisa	255
Pisa	94
Pisa	102
Pisa	103
Pisa	104
Pisa	105
Pisa	106
Pisa	107
Pisa	108
Pisa	109
Pisa	110
Pisa	98
Pisa	287

<tbody>
</tbody>

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

Thank you guys,

 

[Andrew Fergus]

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?

 

[richard1234567891011]

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?

Thank you very much for your answer,

This is the expected result

So,the answer is yes,

City	Sub	Expected result
Roma	1	85000
Pisa	101	385000
Pisa	185	385000
Pisa	186	385000
Pisa	30	317000
Pisa	20	0
Pisa	10	0
Pisa	124	178900
Pisa	254	317000
Pisa	255	317000
Pisa	94	170000
Pisa	102	0
Pisa	103	0
Pisa	104	0
Pisa	105	0
Pisa	106	0
Pisa	107	0
Pisa	108	0
Pisa	109	0
Pisa	110	0
Pisa	98	0
Pisa	287	176700

<tbody>
</tbody>

 

[sandy666]

something like this? (without zeroes)

City	Sub	Values
Roma	1​	85000​
Pisa	30​	317000​
Pisa	94​	170000​
Pisa	98​	176700​
Pisa	101​	385000​
Pisa	124​	178900​
Pisa	185​	385000​
Pisa	186​	385000​
Pisa	254​	317000​
Pisa	255​	317000​
Pisa	287​	176700​

 

[richard1234567891011]

something like this? (without zeroes)

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]City[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Sub[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Values[/COLOR]
Roma	1​	85000​
Pisa	30​	317000​
Pisa	94​	170000​
Pisa	98​	176700​
Pisa	101​	385000​
Pisa	124​	178900​
Pisa	185​	385000​
Pisa	186​	385000​
Pisa	254​	317000​
Pisa	255​	317000​
Pisa	287​	176700​

<tbody>
</tbody>

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

 

[sandy666]

don't quote whole post, use Reply instead of Reply With Quote

Hm, you can transform first table

City	Sub	Values
Roma	1	85000​
Pisa	98	176700​
Pisa	287	176700​
Pisa	59	240000​
Pisa	204	240000​
Pisa	266	240000​
Pisa	127	178900​
Pisa	258	178900​
Pisa	101	385000​
Pisa	185	385000​
Pisa	186	385000​
Pisa	188	17900​
Pisa	124	178900​
Pisa	261	178900​
Pisa	283	32200​
Pisa	284	32200​
Pisa	36	207400​
Pisa	233	207400​
Pisa	30	317000​
Pisa	254	317000​
Pisa	255	317000​
Pisa	34	251800​
Pisa	197	251800​
Pisa	282	251800​
Pisa	94	170000​
Pisa	257	170000​
Pisa	90	191000​
Pisa	275	191000​
Pisa	58	332000​
Pisa	202	332000​
Pisa	203	332000​
Paderno	29	295000​
Paderno	154	295000​
Firenze	28	145000​
Firenze	42	145000​
Firenze	26	148000​
Firenze	44	148000​

is that what you want in first step?

 

[richard1234567891011]

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.

 

[sandy666]

sorry, I don't understand your logic, maybe someone else

have a nice day

 

[richard1234567891011]

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.

 

[sandy666]

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