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

L

Lynderlou

New Member
Joined
Feb 8, 2018
Messages
5
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="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 1108"]
<colgroup><col><col><col><col><col><col span="8"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Output[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Name[/TD]
[TD="align: left"]Car[/TD]
[TD="align: left"]Year[/TD]
[TD="align: left"]Price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Name[/TD]
[TD="align: left"]Car [/TD]
[TD="align: left"]Year[/TD]
[TD="align: left"]Price[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Mark[/TD]
[TD="align: left"]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Mark[/TD]
[TD="align: left"]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Mark, Tom[/TD]
[TD="align: left"]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Mark[/TD]
[TD="align: left"]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]John, Tom[/TD]
[TD="align: left"]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]11000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Tom[/TD]
[TD="align: left"]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Tony[/TD]
[TD="align: left"]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]8000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]John[/TD]
[TD="align: left"]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]John[/TD]
[TD="align: left"]F-150[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Tom[/TD]
[TD="align: left"]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Tony[/TD]
[TD="align: left"]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]John[/TD]
[TD="align: left"]F-150[/TD]
[TD="align: right"]2016[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
</tbody>[/TABLE]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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)

[TABLE="class: grid, width: 529"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Output[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name[/TD]
[TD]Car[/TD]
[TD]Year[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Car[/TD]
[TD]Year[/TD]
[TD]Price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mark[/TD]
[TD]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mark, Tom[/TD]
[TD]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John, Tom[/TD]
[TD]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]11000[/TD]
[TD][/TD]
[TD]Tom[/TD]
[TD]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Tony[/TD]
[TD]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]8000[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]11000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]John[/TD]
[TD]F-150[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[TD]Tom[/TD]
[TD]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]11000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tony[/TD]
[TD]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]8000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]F-150[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]*************[/TD]
[TD]**********[/TD]
[TD]********[/TD]
[TD]***********'[/TD]
[TD]***[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]********[/TD]
[TD]***********[/TD]
[TD]***[/TD]
[/TR]
</tbody>[/TABLE]

I hope this helps.

Markmzz
 
Upvote 0
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
K​
[/td][td]
L​
[/td][td]
M​
[/td][td]
N​
[/td][/tr][tr][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Output[/td][/tr]
[tr][td]
2​
[/td][td]Name[/td][td]Car[/td][td]Year[/td][td]Price[/td][td]Name[/td][td]Car[/td][td]Year[/td][td]Price[/td][/tr]
[tr][td]
3​
[/td][td]Mark[/td][td]Altima[/td][td]
2008​
[/td][td]
10000​
[/td][td]Mark[/td][td]Altima[/td][td]
2008​
[/td][td]
10000​
[/td][/tr]
[tr][td]
4​
[/td][td]Mark, Tom[/td][td]Camry[/td][td]
2010​
[/td][td]
5000​
[/td][td]Mark[/td][td]Camry[/td][td]
2010​
[/td][td]
5000​
[/td][/tr]
[tr][td]
5​
[/td][td]John, Tom[/td][td]Model X[/td][td]
2011​
[/td][td]
11000​
[/td][td]Tom[/td][td]Camry[/td][td]
2010​
[/td][td]
5000​
[/td][/tr]
[tr][td]
6​
[/td][td]Tony[/td][td]Altima[/td][td]
2008​
[/td][td]
8000​
[/td][td]John[/td][td]Model X[/td][td]
2011​
[/td][td]
11000​
[/td][/tr]
[tr][td]
7​
[/td][td]John[/td][td]F-150[/td][td]
2016​
[/td][td]
20000​
[/td][td]Tom[/td][td]Model X[/td][td]
2011​
[/td][td]
11000​
[/td][/tr]
[tr][td]
8​
[/td][td]Dow, Tom[/td][td]Camry[/td][td]
2010​
[/td][td]
1,000,000​
[/td][td]Tony[/td][td]Altima[/td][td]
2008​
[/td][td]
8000​
[/td][/tr]
[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td]John[/td][td]F-150[/td][td]
2016​
[/td][td]
20000​
[/td][/tr]
[/table]

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))
 
Upvote 0
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
 
Last edited:
Upvote 0
markmzz said:
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)

[TABLE="class: grid, width: 529"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Output[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name[/TD]
[TD]Car[/TD]
[TD]Year[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Car[/TD]
[TD]Year[/TD]
[TD]Price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mark[/TD]
[TD]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mark, Tom[/TD]
[TD]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John, Tom[/TD]
[TD]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]11000[/TD]
[TD][/TD]
[TD]Tom[/TD]
[TD]Camry[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Tony[/TD]
[TD]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]8000[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]11000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]John[/TD]
[TD]F-150[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[TD]Tom[/TD]
[TD]Model X[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]11000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tony[/TD]
[TD]Altima[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]8000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]F-150[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]*************[/TD]
[TD]**********[/TD]
[TD]********[/TD]
[TD]***********'[/TD]
[TD]***[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]********[/TD]
[TD]***********[/TD]
[TD]***[/TD]
[/TR]
</tbody>[/TABLE]

I hope this helps.

Markmzz
Click to expand...

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.
 
Upvote 0
Lynderlou said:
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.
Click to expand...

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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
Lynderlou said:
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
Click to expand...

Hi again,

I think I found the problem.

Maybe the Calculation Options is setup to Manual.

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Multiple Search Criteria in Excel Table
Replies
4
Views
125
KOKOSEK
KOKOSEK
A
  • Question Question
Need to convert value depending on currency and date
Replies
2
Views
467
deLockloire
D
N
  • Question Question
Color duplicates rows in two different sets of data and also the rows which are present in both sets of data.
Replies
0
Views
463
Noob_Excel
N
S
  • Question Question
Help on complicated VBA code
Replies
3
Views
749
superfb
S
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
544
skyh3ck
skyh3ck

Forum statistics

Threads
1,223,705
Messages
6,173,986
Members
452,541
Latest member
haasro02

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