Find A Value based on multiple columns and single cells with data separated by a comma

[Lynderlou]

Hi

I have a spreadsheet with a set of criteria in it. I have called it table 1

I have a second set of data I will call Table 2.

I would like to be able to insert the appropriate value in the PRICE column in table 2 based on the criteria listed in table 1 and matching it to a set of values found in table 1 vs 2. Perhaps just showing what I am trying to do is best as I think I am not being clear in my description. Any help would be appreciated

Table 1 Table 2 Output Name Car Year Price Name Car Year Price Mark Altima 2008 10000 Mark Altima 2008 Mark, Tom Camry 2010 5000 Mark Camry 2010 John, Tom Model X 2011 11000 Tom Camry 2010 Tony Altima 2008 8000 John Model X 2011 John F-150 2016 20000 Tom Model X 2011 Tony Altima 2008 John F-150 2016 <colgroup><col><col><col><col><col><col span="8"><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[markmzz]

Hello!

Try this formula in I4 and copy down:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$F4&" "," "&SUBSTITUTE($A$4:$A$8,",","")&" ")),--($B$4:$B$8=$G4),--($C$4:$C$8=$H4),$D$4:$D$8)

	A	B	C	D	E	F	G	H	I	J
1	Table 1					Table 2
2									Output
3	Name	Car	Year	Price		Name	Car	Year	Price
4	Mark	Altima	2008	10000		Mark	Altima	2008	10000
5	Mark, Tom	Camry	2010	5000		Mark	Camry	2010	5000
6	John, Tom	Model X	2011	11000		Tom	Camry	2010	5000
7	Tony	Altima	2008	8000		John	Model X	2011	11000
8	John	F-150	2016	20000		Tom	Model X	2011	11000
9						Tony	Altima	2008	8000
10						John	F-150	2016	20000
****	*************	**********	********	***********'	***	**********	**********	********	***********	***

<tbody>
</tbody>

I hope this helps.

Markmzz

 

[Aladin Akyurek]

Row\Col	C​	D​	E​	F​	K​	L​	M​	N​
1​								Output
2​	Name	Car	Year	Price	Name	Car	Year	Price
3​	Mark	Altima	2008​	10000​	Mark	Altima	2008​	10000​
4​	Mark, Tom	Camry	2010​	5000​	Mark	Camry	2010​	5000​
5​	John, Tom	Model X	2011​	11000​	Tom	Camry	2010​	5000​
6​	Tony	Altima	2008​	8000​	John	Model X	2011​	11000​
7​	John	F-150	2016​	20000​	Tom	Model X	2011​	11000​
8​	Dow, Tom	Camry	2010​	1,000,000​	Tony	Altima	2008​	8000​
9​					John	F-150	2016​	20000​

In N3 control+shift+enter, not just enter, and copy down:

=INDEX($F$3:$F$8,MATCH(M3,IF(ISNUMBER(SEARCH(","&K3&",",","&SUBSTITUTE($C$3:$C$8," ","")&",")),IF($D$3:$D$8=L3,$E$3:$E$8)),0))

 

[markmzz]

Try this new formulas too:

Normal Formula

=SUMPRODUCT(--ISNUMBER(SEARCH(", "&F4&", ",", "&$A$4:$A$8&", ")),--($B$4:$B$8=G4),--($C$4:$C$8=H4),$D$4:$D$8)

Array Formula (use Ctrl+Shift+Enter and not only Enter to enter the formula)

=SUM(ISNUMBER(SEARCH(", "&F4&", ",", "&IF($B$4:$B$8=G4,IF($C$4:$C$8=H4,$A$4:$A$8))&", "))*$D$4:$D$8)

Markmzz

 

[Lynderlou]

Hello!

Try this formula in I4 and copy down:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$F4&" "," "&SUBSTITUTE($A$4:$A$8,",","")&" ")),--($B$4:$B$8=$G4),--($C$4:$C$8=$H4),$D$4:$D$8)

	A	B	C	D	E	F	G	H	I	J
1	Table 1					Table 2
2									Output
3	Name	Car	Year	Price		Name	Car	Year	Price
4	Mark	Altima	2008	10000		Mark	Altima	2008	10000
5	Mark, Tom	Camry	2010	5000		Mark	Camry	2010	5000
6	John, Tom	Model X	2011	11000		Tom	Camry	2010	5000
7	Tony	Altima	2008	8000		John	Model X	2011	11000
8	John	F-150	2016	20000		Tom	Model X	2011	11000
9						Tony	Altima	2008	8000
10						John	F-150	2016	20000
****	*************	**********	********	***********'	***	**********	**********	********	***********	***

<tbody>
</tbody>

I hope this helps.

Markmzz

Hi Markmzz - This helped but....I pasted the formula into I4, when I copied it down all the results were the same (10000). But if I look at the formula for each cell, the formula box shows what the correct value should have been and once I close the formula box it places the correct value into the cell. I'm not sure why the formula copied correctly but the values remained the same until I examined each formula. I grabbed the bottom right corner of I4 and dragged it I10 to do the copy.

 

[Aladin Akyurek]

Try post #3 ...

 

[markmzz]

Hi Markmzz - This helped but....I pasted the formula into I4, when I copied it down all the results were the same (10000). But if I look at the formula for each cell, the formula box shows what the correct value should have been and once I close the formula box it places the correct value into the cell. I'm not sure why the formula copied correctly but the values remained the same until I examined each formula. I grabbed the bottom right corner of I4 and dragged it I10 to do the copy.

Hi Lynderlou,

I didn't understand. Here all is ok.

Try the formula below and tell me if it works for you:

=SUMPRODUCT(--ISNUMBER(SEARCH(", "&F4&", ",", "&$A$4:$A$8&", ")),--($B$4:$B$8=G4),--($C$4:$C$8=H4),$D$4:$D$8)

Markmzz

 

[Lynderlou]

Hi Markmzz - Ok I dresearched some more and found if I have all the cells highlighted when I enter the formula into the formula bar and hit CTL-Enter. All values are correctly inserted.

Thanks for your help with this!

 

[Lynderlou]

This formula gave me the same results but if I highlight all cells first and the enter the formula with a CTL-Enter, it also populates the correct values

 

[markmzz]

This formula gave me the same results but if I highlight all cells first and the enter the formula with a CTL-Enter, it also populates the correct values

Hi again,

I think I found the problem.

Maybe the Calculation Options is setup to Manual.

Markmzz