Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Hi Helpppp!
Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Try this:
=SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Thanks for the information, however if the base data in Sheet 1 changes,
meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
I see that you've posted this a few times and you're having a hard time
understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Hi
Yes I accidently posted twice. Anyway, I have tried the below formula however I get back a #value! in the cell, the formula is =SUMPRODUCT(--(Budget!$A$12:$A$121=A6)--(Budget!$B$12:$B$121=B6),Budget!$C$12:$C$1211), have I done a typo do you think, what does #value mean? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
The formula that you have posted either has a typo but if it's not a typo
then I can see why you're getting an error: All ranges must be the same size: Budget!$A$12:$A$121 Budget!$B$12:$B$121 Budget!$C$12:$C$1211 Budget!$C$12:$C$1211 is not the same size as the other ranges. This will cause a #VALUE! error. Biff "Cinny" wrote in message ... Hi Yes I accidently posted twice. Anyway, I have tried the below formula however I get back a #value! in the cell, the formula is =SUMPRODUCT(--(Budget!$A$12:$A$121=A6)--(Budget!$B$12:$B$121=B6),Budget!$C$12:$C$1211), have I done a typo do you think, what does #value mean? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Sorry I forgot to ask, does it make a difference to the formula if the
columns A & B are text fields? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Thank you so much, silly typo was effecting the formula, I appreciate your
patience. C "T. Valko" wrote: The formula that you have posted either has a typo but if it's not a typo then I can see why you're getting an error: All ranges must be the same size: Budget!$A$12:$A$121 Budget!$B$12:$B$121 Budget!$C$12:$C$1211 Budget!$C$12:$C$1211 is not the same size as the other ranges. This will cause a #VALUE! error. Biff "Cinny" wrote in message ... Hi Yes I accidently posted twice. Anyway, I have tried the below formula however I get back a #value! in the cell, the formula is =SUMPRODUCT(--(Budget!$A$12:$A$121=A6)--(Budget!$B$12:$B$121=B6),Budget!$C$12:$C$1211), have I done a typo do you think, what does #value mean? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Makes no difference.
Biff "Cinny" wrote in message ... Sorry I forgot to ask, does it make a difference to the formula if the columns A & B are text fields? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
I hope that means you got it working?
You're welcome! Biff "Cinny" wrote in message ... Thank you so much, silly typo was effecting the formula, I appreciate your patience. C "T. Valko" wrote: The formula that you have posted either has a typo but if it's not a typo then I can see why you're getting an error: All ranges must be the same size: Budget!$A$12:$A$121 Budget!$B$12:$B$121 Budget!$C$12:$C$1211 Budget!$C$12:$C$1211 is not the same size as the other ranges. This will cause a #VALUE! error. Biff "Cinny" wrote in message ... Hi Yes I accidently posted twice. Anyway, I have tried the below formula however I get back a #value! in the cell, the formula is =SUMPRODUCT(--(Budget!$A$12:$A$121=A6)--(Budget!$B$12:$B$121=B6),Budget!$C$12:$C$1211), have I done a typo do you think, what does #value mean? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Hi Again
Problem with the formula result, as column a has duplicate entries & b also has duplicate entries, the result is giving me incorrect financial data as it is adding all the duplicates up. What my data is made up of is multiple different customers with multiple different industries, but a customer might be in more than one industry and an industry might have more than one customer. Therefore you see my problem, is an index/match formula an alternative for this problem????? "T. Valko" wrote: The formula that you have posted either has a typo but if it's not a typo then I can see why you're getting an error: All ranges must be the same size: Budget!$A$12:$A$121 Budget!$B$12:$B$121 Budget!$C$12:$C$1211 Budget!$C$12:$C$1211 is not the same size as the other ranges. This will cause a #VALUE! error. Biff "Cinny" wrote in message ... Hi Yes I accidently posted twice. Anyway, I have tried the below formula however I get back a #value! in the cell, the formula is =SUMPRODUCT(--(Budget!$A$12:$A$121=A6)--(Budget!$B$12:$B$121=B6),Budget!$C$12:$C$1211), have I done a typo do you think, what does #value mean? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Hi
Not sure if you got to read this but, I'm having a problem with the formula where it is adding up amounts for a customer every time the customer is mentioned, for example Telstra occurs 3 times as it is classified under 3 different industries therefore giving back the incorrect amount. Problem with the formula result, as column A has duplicate entries & b also has duplicate entries, the result is giving me incorrect financial data as it is adding all the duplicates up. What my data is made up of is multiple different customers with multiple different industries, but a customer might be in more than one industry and an industry might have more than one customer. Therefore you see my problem, is an index/match formula an alternative for this problem????? "T. Valko" wrote: Makes no difference. Biff "Cinny" wrote in message ... Sorry I forgot to ask, does it make a difference to the formula if the columns A & B are text fields? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
is an index/match formula an alternative for this problem?????
Yes, but it's more complicated! =INDEX(Budget!$C$12:$C$121,MATCH(1,(Budget!$A$12:$ A$121=A6)*(Budget!$B$12:$B$121=B6),0)) This is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) That is, hold down both the CTRL key and the SHIFT key then hit ENTER. If done properly Excel will enclose the formula in squiggly brackets {....}. You can't just type these brackets in, you *MUST* use the key combo. Biff "Cinny" wrote in message ... Hi Again Problem with the formula result, as column a has duplicate entries & b also has duplicate entries, the result is giving me incorrect financial data as it is adding all the duplicates up. What my data is made up of is multiple different customers with multiple different industries, but a customer might be in more than one industry and an industry might have more than one customer. Therefore you see my problem, is an index/match formula an alternative for this problem????? "T. Valko" wrote: The formula that you have posted either has a typo but if it's not a typo then I can see why you're getting an error: All ranges must be the same size: Budget!$A$12:$A$121 Budget!$B$12:$B$121 Budget!$C$12:$C$1211 Budget!$C$12:$C$1211 is not the same size as the other ranges. This will cause a #VALUE! error. Biff "Cinny" wrote in message ... Hi Yes I accidently posted twice. Anyway, I have tried the below formula however I get back a #value! in the cell, the formula is =SUMPRODUCT(--(Budget!$A$12:$A$121=A6)--(Budget!$B$12:$B$121=B6),Budget!$C$12:$C$1211), have I done a typo do you think, what does #value mean? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Hi
Thanks that is perfect, all is right in the world now... appreciate your help. "T. Valko" wrote: is an index/match formula an alternative for this problem????? Yes, but it's more complicated! =INDEX(Budget!$C$12:$C$121,MATCH(1,(Budget!$A$12:$ A$121=A6)*(Budget!$B$12:$B$121=B6),0)) This is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) That is, hold down both the CTRL key and the SHIFT key then hit ENTER. If done properly Excel will enclose the formula in squiggly brackets {....}. You can't just type these brackets in, you *MUST* use the key combo. Biff "Cinny" wrote in message ... Hi Again Problem with the formula result, as column a has duplicate entries & b also has duplicate entries, the result is giving me incorrect financial data as it is adding all the duplicates up. What my data is made up of is multiple different customers with multiple different industries, but a customer might be in more than one industry and an industry might have more than one customer. Therefore you see my problem, is an index/match formula an alternative for this problem????? "T. Valko" wrote: The formula that you have posted either has a typo but if it's not a typo then I can see why you're getting an error: All ranges must be the same size: Budget!$A$12:$A$121 Budget!$B$12:$B$121 Budget!$C$12:$C$1211 Budget!$C$12:$C$1211 is not the same size as the other ranges. This will cause a #VALUE! error. Biff "Cinny" wrote in message ... Hi Yes I accidently posted twice. Anyway, I have tried the below formula however I get back a #value! in the cell, the formula is =SUMPRODUCT(--(Budget!$A$12:$A$121=A6)--(Budget!$B$12:$B$121=B6),Budget!$C$12:$C$1211), have I done a typo do you think, what does #value mean? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup.... 2nd time
Good deal!
Thanks for the feedback! Biff "Cinny" wrote in message ... Hi Thanks that is perfect, all is right in the world now... appreciate your help. "T. Valko" wrote: is an index/match formula an alternative for this problem????? Yes, but it's more complicated! =INDEX(Budget!$C$12:$C$121,MATCH(1,(Budget!$A$12:$ A$121=A6)*(Budget!$B$12:$B$121=B6),0)) This is an array formula. Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) That is, hold down both the CTRL key and the SHIFT key then hit ENTER. If done properly Excel will enclose the formula in squiggly brackets {....}. You can't just type these brackets in, you *MUST* use the key combo. Biff "Cinny" wrote in message ... Hi Again Problem with the formula result, as column a has duplicate entries & b also has duplicate entries, the result is giving me incorrect financial data as it is adding all the duplicates up. What my data is made up of is multiple different customers with multiple different industries, but a customer might be in more than one industry and an industry might have more than one customer. Therefore you see my problem, is an index/match formula an alternative for this problem????? "T. Valko" wrote: The formula that you have posted either has a typo but if it's not a typo then I can see why you're getting an error: All ranges must be the same size: Budget!$A$12:$A$121 Budget!$B$12:$B$121 Budget!$C$12:$C$1211 Budget!$C$12:$C$1211 is not the same size as the other ranges. This will cause a #VALUE! error. Biff "Cinny" wrote in message ... Hi Yes I accidently posted twice. Anyway, I have tried the below formula however I get back a #value! in the cell, the formula is =SUMPRODUCT(--(Budget!$A$12:$A$121=A6)--(Budget!$B$12:$B$121=B6),Budget!$C$12:$C$1211), have I done a typo do you think, what does #value mean? "T. Valko" wrote: I see that you've posted this a few times and you're having a hard time understanding the replies you've recieved. Have you actually tried any of the suggestions? Did any of the suggestions work? If none of the suggestions worked then we are not understanding what you want to do. Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? The formula tests the contents of the cells on sheet2 to see which, if any, match the cells on sheet1. If you change the contents of the cells on either sheet it will not affect how the formula works. Biff "Cinny" wrote in message ... Thanks for the information, however if the base data in Sheet 1 changes, meaning customer name and industry how does the formula no that it needs to look for a different value in sheet 2? M "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!A$6:A$51=A6)--(Sheet2!B$6:B$51=B6),Sheet2!I$6:I$51) Copy down as needed. Biff "Cinny" wrote in message ... Hi Helpppp! Sheet 1 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in Column H6 to H51 I need a result based on the first two criteria Customer name and industry region from Sheet 2 Sheet 2 is something like this Colum A6 to A51 is a customer name Column B6 to B51 is a industry region in column I6 to I51 I have the budget figure than needs to go back into sheet 1. I can not join the two criteria because of an interactive report i need to generate later. Thanks for any help. Cinny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using rounded time with vlookup | Excel Discussion (Misc queries) | |||
First Time Error Using VLOOKUP | Excel Discussion (Misc queries) | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |