Can this be done?

[geolefty]

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

[RET79]

Can you explain further please I don't fully understand what you mean, especially when you say 2nd and 3rd column matching???

RET79

[geolefty]

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.

[Aladin Akyurek]

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.

[geolefty]

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

[Yogi Anand]

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

[geolefty]

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

[Yogi Anand]

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!