vlookup problem
 

Hi there,

I have a table with machine specifications.
There are machines with same name, but different length and therefore
different prices.

I'm looking for a vlookup formula (or any other solution) which can
check for both: name and length and give me back the price.
(I don't like the suggestion to have a cell with name and length as
one entry, as the table is imported from a different source)

A B C
1 name length price
2 ABD 2000 1850
3 ABD 2500 2050
4 ABD 3000 2250
5 EFG 2000 3285
6 EFG 2500 3568
7 EFG 3000 3859


which price has the machine ABD with a length of 2500mm?

Any help is very much appreciated.

Best regards,
Norbert Jaeger

vlookup problem
 

one way to try
=SUMPRODUCT((E1:E21="ABD")*(F1:F21=3000)*G1:G21)

--
Don Guillett
SalesAid Software

"Orion" wrote in message
...
Hi there,

I have a table with machine specifications.
There are machines with same name, but different length and therefore
different prices.

I'm looking for a vlookup formula (or any other solution) which can
check for both: name and length and give me back the price.
(I don't like the suggestion to have a cell with name and length as
one entry, as the table is imported from a different source)

A B C
1 name length price
2 ABD 2000 1850
3 ABD 2500 2050
4 ABD 3000 2250
5 EFG 2000 3285
6 EFG 2500 3568
7 EFG 3000 3859


which price has the machine ABD with a length of 2500mm?

Any help is very much appreciated.

Best regards,
Norbert Jaeger

vlookup problem
 

Orion,

If you use VLOOUP, what I did in the past was concatenated the two columns
and concatenated the search string in order to look at more that one column.
However, what I think you should be using is DGET. You set up criteria
similar to this..

A B
1 name length
2 ABD 2500
3 name length
4 ABD 3000

Then the formula looks something like this...
=DGET(Spreadsheet1!A1:C7,Spreadsheet2!A1:B2,Spread sheet1!C1)

I think this is right. You might want to look at the function in more
detail in Excel Help.

HTH, Dean.


"Orion" wrote:

Hi there,

I have a table with machine specifications.
There are machines with same name, but different length and therefore
different prices.

I'm looking for a vlookup formula (or any other solution) which can
check for both: name and length and give me back the price.
(I don't like the suggestion to have a cell with name and length as
one entry, as the table is imported from a different source)

A B C
1 name length price
2 ABD 2000 1850
3 ABD 2500 2050
4 ABD 3000 2250
5 EFG 2000 3285
6 EFG 2500 3568
7 EFG 3000 3859


which price has the machine ABD with a length of 2500mm?

Any help is very much appreciated.

Best regards,
Norbert Jaeger

vlookup problem
 

Thanks a lot. It works.

Regards,
Norbert



On Mon, 29 Nov 2004 08:11:21 -0600, "Don Guillett"
wrote:

one way to try
=SUMPRODUCT((E1:E21="ABD")*(F1:F21=3000)*G1:G21 )

vlookup problem
 

glad to help

--
Don Guillett
SalesAid Software

"Orion" wrote in message
...
Thanks a lot. It works.

Regards,
Norbert



On Mon, 29 Nov 2004 08:11:21 -0600, "Don Guillett"
wrote:

one way to try
=SUMPRODUCT((E1:E21="ABD")*(F1:F21=3000)*G1:G21 )

vlookup problem
 

The formula below from Don helped me a lot, but now I have another
problem:

this time my table looks like this:
A B C
1 Cairo CA D830-76
2 Cairo D831-76
3 Paris CA C870-76
4 Paris C871-76
5 New York CA D816-76
6 New York D817-76

in column A: quality name
in column B: pattern info
in column C: quality number

my input cells:

D1: Cairo
E1: CA M 4

whenever the pattern info starts with a 'CA' the result must show the
quality number of the right quality name and the pattern info 'CA'.
It the pattern info starts with something else than 'CA' the result
must be the quality number from column C, where the cell in column B
is empty.

for my example:
result should be: 'D830-76', because E1 starts with 'CA'
in case E1 is 'PB COL 1', the result must be D831-76, because E1
starts with something else than 'CA'

It seems to be a problem, that the cell entries of the cells I want to
get the result from (C1:C6) is text and not numbers.

I tried following as a start:

=sumproduct((A1:A6=D1)*(B1:B6=left(E1,2))*C1:C6)

I always end up with #VALUE and I'm not even close to my other
problem, to cover entries for E1 starting with something else than
'CA'.

My head is spinning, my neck is sore ....
Please can anyone help me out

Regards,
Norbert



On Mon, 29 Nov 2004 08:11:21 -0600, "Don Guillett"
wrote:

one way to try
=SUMPRODUCT((E1:E21="ABD")*(F1:F21=3000)*G1:G21 )

--
Don Guillett
SalesAid Software

"Orion" wrote in message
.. .
Hi there,

I have a table with machine specifications.
There are machines with same name, but different length and therefore
different prices.

I'm looking for a vlookup formula (or any other solution) which can
check for both: name and length and give me back the price.
(I don't like the suggestion to have a cell with name and length as
one entry, as the table is imported from a different source)

A B C
1 name length price
2 ABD 2000 1850
3 ABD 2500 2050
4 ABD 3000 2250
5 EFG 2000 3285
6 EFG 2500 3568
7 EFG 3000 3859


which price has the machine ABD with a length of 2500mm?

Any help is very much appreciated.

Best regards,
Norbert Jaeger

vlookup problem
 

I am unable to find the original message. You have pattern info that reads
CA more than once in your data set. Must you match both the "quality name"
and the "pattern name". If so, I'd suggest concatenating columns A and B
and using that as your lookup variable.

"Orion" wrote in message
...
The formula below from Don helped me a lot, but now I have another
problem:

this time my table looks like this:
A B C
1 Cairo CA D830-76
2 Cairo D831-76
3 Paris CA C870-76
4 Paris C871-76
5 New York CA D816-76
6 New York D817-76

in column A: quality name
in column B: pattern info
in column C: quality number

my input cells:

D1: Cairo
E1: CA M 4

whenever the pattern info starts with a 'CA' the result must show the
quality number of the right quality name and the pattern info 'CA'.
It the pattern info starts with something else than 'CA' the result
must be the quality number from column C, where the cell in column B
is empty.

for my example:
result should be: 'D830-76', because E1 starts with 'CA'
in case E1 is 'PB COL 1', the result must be D831-76, because E1
starts with something else than 'CA'

It seems to be a problem, that the cell entries of the cells I want to
get the result from (C1:C6) is text and not numbers.

I tried following as a start:

=sumproduct((A1:A6=D1)*(B1:B6=left(E1,2))*C1:C6)

I always end up with #VALUE and I'm not even close to my other
problem, to cover entries for E1 starting with something else than
'CA'.

My head is spinning, my neck is sore ....
Please can anyone help me out

Regards,
Norbert



On Mon, 29 Nov 2004 08:11:21 -0600, "Don Guillett"
wrote:

one way to try
=SUMPRODUCT((E1:E21="ABD")*(F1:F21=3000)*G1:G21 )

--
Don Guillett
SalesAid Software

"Orion" wrote in message
.. .
Hi there,

I have a table with machine specifications.
There are machines with same name, but different length and therefore
different prices.

I'm looking for a vlookup formula (or any other solution) which can
check for both: name and length and give me back the price.
(I don't like the suggestion to have a cell with name and length as
one entry, as the table is imported from a different source)

A B C
1 name length price
2 ABD 2000 1850
3 ABD 2500 2050
4 ABD 3000 2250
5 EFG 2000 3285
6 EFG 2500 3568
7 EFG 3000 3859


which price has the machine ABD with a length of 2500mm?

Any help is very much appreciated.

Best regards,
Norbert Jaeger