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.


[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,

 

[richard1234567891011]

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

You Are stuck with this idea of merging tables, this is not what I am asking,

I cannot merge, I need a formula,

Thank you,

 

[sandy666]

you can merge via PowerQuery

if you want formula - I can't help

have a nice day

 

[Andrew Fergus]

Thanks for your reply. I see what you are trying to do but your data layout is making this very difficult. Are there options to split the data in the first table? Let me explain why. Let's say I'm trying to match both Pisa and 10. The way the data is organised in the first table with the city and code(s) as a string in a single cell means that if I try to match Pisa+10 then the formula will not be able to tell the difference between say Pisa 10 - 20 and Pisa 101 - 201. In finding Pisa+10 it would get an incorrect match with Pisa+101 (I know that data does not exist yet, it is an example). Best practice would be to have different pieces of data in separate columns, rather than all being in 1 column.

The options from here are:
1) a VBA custom function to do the match
2) splitting the data in the first table so that it is almost normalised (each column has it's own piece of data, not multiple pieces of data)
3) using a number of helper columns in the first table to achieve the same effect as number 2 if number 2 is not possible.

How much data is there? Are you using the Excel Tables functionality? Lastly, are you entering the data or is it being sourced from somewhere else?

 

[Eric W]

I agree with Andrew that it's better to split out the individual fields into separate columns. But if that's not an option (imported data), then this might work:

Book1
	A	B	C	D	E	F
1		Values		City	Sub	Result
2	Roma Sub 1	85000		Roma	1	85000
3	Pisa Sub 98 - 287	176700		Pisa	101	385000
4	Pisa Sub 59 - 204 - 266	240000		Pisa	185	385000
5	Pisa Sub 127 - 258	178900		Pisa	186	385000
6	Pisa Sub 101-185-186	385000		Pisa	30	317000
7	Pisa Sub 188	17900		Pisa	20	0
8	Pisa Sub 124 - 261	178900		Pisa	10	0
9	Pisa Sub 283 - 284	32200		Pisa	124	178900
10	Pisa Sub 36 - 233	207400		Pisa	254	317000
11	Pisa Sub 30 - 254 - 255	317000		Pisa	255	317000
12	Pisa Sub 34 - 197 - 282	251800		Pisa	94	170000
13	Pisa Sub 94 - 257	170000		Pisa	102	0
14	Pisa Sub 90-275	191000		Pisa	103	0
15	Pisa Sub 58 - 202 - 203	332000		Pisa	104	0
16	Paderno Sub 29 - 154	295000		Pisa	105	0
17	Firenze Sub 28 - 42	145000		Pisa	106	0
18	Firenze Sub 26 - 44	148000		Pisa	107	0
19				Pisa	108	0
20				Pisa	109	0
21				Pisa	110	0
22				Pisa	98	176700
23				Pisa	287	176700
Sheet11

Cell Formulas
Range	Formula
F2	=IFERROR(AGGREGATE(15,6,$B$2:$B$18/ISNUMBER(SEARCH(D2,$A$2:$A$18))/ISNUMBER(SEARCH(" "&E2&" ",SUBSTITUTE($A$2:$A$18,"-"," ")&" ")),1),0)