Can this be done?

geolefty

Board Regular
Joined
Mar 6, 2002
Messages
180
I think it can be but I am struggling.

In the example below, I want to use the $400 in the 'formula' spots. I know the first column doesn't match but need to lookup the $400 based on the 2nd and 3rd columns matching.

dog five car $400
cat five bus 'formula'
pig five car 'formula'
bird five car 'formula'

thanks
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Can you explain further please I don't fully understand what you mean, especially when you say 2nd and 3rd column matching???

RET79
 
Upvote 0
In the example, the 3rd and 4th rows both have 'five' in the 2nd column and 'car' in the 3rd.

I need these rows to essentially lookup rows with the same specs (five, car) and pull that $400.
 
Upvote 0
On 2002-05-01 17:04, geolefty wrote:
In the example, the 3rd and 4th rows both have 'five' in the 2nd column and 'car' in the 3rd.

I need these rows to essentially lookup rows with the same specs (five, car) and pull that $400.

In E2 enter: five
In F2 enter: car

=SUMPRODUCT((B2:B20=E2)*(C2:C20=F2),D2:D20)

Or use DSUM, which requires the appropriate labels from your data in E1 and F1 in addition to conditions in E2 and F2:

=DSUM(Database,4,E1:F2)

where Database refers to your data range including labels/column headings.

You could also consider using Pivot Tables.
 
Upvote 0
Thanks Aladin I think we could be on the right track although I think I might be trying to do something almost impossible.

Although if anyone can figure it out it is you and others here.

I tried to use the sumproduct in the same column as the number I am trying to lookup and I get circular refs.

Essentially some of the rows have pricing I want to use (like the $400) when related to 'dog'. The other rows I need to pull that pricing when the other columns match (five, car) but do not match 'dog'.

i am trying in a seperate column, but that can be a pain since i have about 12 diff column to do this to.

any other ideas?

thanks
thank again
 
Upvote 0
On 2002-05-01 16:48, geolefty wrote:
I think it can be but I am struggling.

In the example below, I want to use the $400 in the 'formula' spots. I know the first column doesn't match but need to lookup the $400 based on the 2nd and 3rd columns matching.

dog five car $400
cat five bus 'formula'
pig five car 'formula'
bird five car 'formula'

thanks

If I understand you correctly, if the entry in the second column of rows 2,3,and 4th column is 'five', then you would want the corresponding entry in the 4th column
to be the same as the entry in the 4th column of row 1 … if it is so, then use
the following formula in the 4th column of rows 2,3, and 4 --

=VLOOKUP("five",{"five","car",400},3)

If I have not understood you correctly, or it does not work for you, please explain a little further ... and let us take it from there!

Regards
 
Upvote 0
I need the $400 in other rows if column 2 AND 3 match.
One additional problem is that the $400 is not constant.

Let me try to explain a little further with more specific data.
I have regional data (5 regions) and I have north america pricing. For any of the other regions (not north america) I want to take the NA pricing and factor up or down from it. I need to take the NA pricing for similar data. I need to take the NA price for a 12-inch TV and use that to factor the price for a Europe 12-inch TV. and NA 14-inch TV to get the Europe 14-inch price.

I have many products and lots of pricing so I wanted something pretty dynamic and I just dont know if I can get it.

My thought is to check and see if a cell is north america, if so then use than pricing. If not then check other characteristics and make sure they are similar to use for the comparison.

It sounds simple to me but I am just stuck.

thanks
 
Upvote 0
I believe your data is too extensive to explain in a few words on the board. You should either post some real data on the board so the viewers can bite into the real thing. If you care to email your file to me, I will be glad to look at it and help if i can.

Regards!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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