Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup problem
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup problem | New Users to Excel | |||
vlookup problem | Excel Discussion (Misc queries) | |||
Vlookup problem | Excel Worksheet Functions | |||
vlookup problem | Excel Worksheet Functions | |||
VLOOKUP problem | Excel Worksheet Functions |