Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple col
I have raw data that I put into a spreadsheet that I need to match to a
pre-existing "legend". Legend Set-Up: Column A - Storefront Name Column B - Publisher Name Column C - Placement Name Column D - Orders I need to automatically insert data into Column D (Orders) by searching AND matching (exactly) all three Columns A-C in the "raw data" tab. I realize that VLOOKUP will only search the first Column in my Legends tab. Now I am at a complete loss how to achieve the desired outcome. Any ideas on how to do this would be really appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple col
Try Code: -------------------- =INDEX('raw data'!$D$2:$D$100,MATCH(1,INDEX(('raw data'!$A$2:$A$100=A2)*('raw data'!$B$2:$B$100=B2)*('raw data'!$C$2:$C$100=C2),0),0)) -------------------- adjust ranges to suit and copied down. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109856 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple col
You can use SUMPRODUCT
If returning numbers: =SUMPRODUCT(('Raw Data'!A$2:A$100=A2)*('Raw Data'!B$2:B$100=B2)*('Raw Data'!C$2:C$100)*('Raw Data'!D$2:D$100)) If you're trying to return text: =INDEX('Raw Data'!D:D,SUMPRODUCT(('Raw Data'!A$2:A$100=A2)*('Raw Data'!B$2:B$100=B2)*('Raw Data'!C$2:C$100)*(ROW('Raw Data'!A$2:A$100)))) Adjust range sizes as appropriate. Note that you can't callout the entire column inside the SUMPRODUCT function unless you have XL 2007. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MMangen" wrote: I have raw data that I put into a spreadsheet that I need to match to a pre-existing "legend". Legend Set-Up: Column A - Storefront Name Column B - Publisher Name Column C - Placement Name Column D - Orders I need to automatically insert data into Column D (Orders) by searching AND matching (exactly) all three Columns A-C in the "raw data" tab. I realize that VLOOKUP will only search the first Column in my Legends tab. Now I am at a complete loss how to achieve the desired outcome. Any ideas on how to do this would be really appreciated! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple col
Hi,
What exactly are you trying to return? Is it numeric data or text? Do you want to count or sum the data? Can there be more than one item that matches all three conditions? Here is an example: =SUMPRODUCT(--(A$2:A$16=M1),--(B$2:B$16=M2),--(C$2:C$16=M3),D2:D16) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "MMangen" wrote: I have raw data that I put into a spreadsheet that I need to match to a pre-existing "legend". Legend Set-Up: Column A - Storefront Name Column B - Publisher Name Column C - Placement Name Column D - Orders I need to automatically insert data into Column D (Orders) by searching AND matching (exactly) all three Columns A-C in the "raw data" tab. I realize that VLOOKUP will only search the first Column in my Legends tab. Now I am at a complete loss how to achieve the desired outcome. Any ideas on how to do this would be really appreciated! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple
Wow, intense! Okay, I'm wondering if something is still off as I'm getting a
#N/A and there is data in the referenced column (unless a reference is off). Should any part of that formula reference the Legend Tab? Also, if it's easier maybe I can call you? Whatever works! "NBVC" wrote: Try Code: -------------------- =INDEX('raw data'!$D$2:$D$100,MATCH(1,INDEX(('raw data'!$A$2:$A$100=A2)*('raw data'!$B$2:$B$100=B2)*('raw data'!$C$2:$C$100=C2),0),0)) -------------------- adjust ranges to suit and copied down. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109856 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple
Luke:
Thank you so much for taking your time to respond to my question. I am using Excel 2007 and trying to return a number. I may not have been entirely clear in my original question (I am getting a Value error right now): In the Legend Tab I need to have the number pulled from the Raw tab if Columns A-C have an identical match in the Raw tab. The number that needs to be retrieved is in the Raw tab (needs to be inserted into Column D in the Legend Tab. So for example: In Legend Tab: Column A - red Column B - blue Column C - yellow Then I need for the formula to go search in Raw tab to see the number of items that match all three of the above referenced criteria - in this case 9. Hope this helps more. Michelle "Luke M" wrote: You can use SUMPRODUCT If returning numbers: =SUMPRODUCT(('Raw Data'!A$2:A$100=A2)*('Raw Data'!B$2:B$100=B2)*('Raw Data'!C$2:C$100)*('Raw Data'!D$2:D$100)) If you're trying to return text: =INDEX('Raw Data'!D:D,SUMPRODUCT(('Raw Data'!A$2:A$100=A2)*('Raw Data'!B$2:B$100=B2)*('Raw Data'!C$2:C$100)*(ROW('Raw Data'!A$2:A$100)))) Adjust range sizes as appropriate. Note that you can't callout the entire column inside the SUMPRODUCT function unless you have XL 2007. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MMangen" wrote: I have raw data that I put into a spreadsheet that I need to match to a pre-existing "legend". Legend Set-Up: Column A - Storefront Name Column B - Publisher Name Column C - Placement Name Column D - Orders I need to automatically insert data into Column D (Orders) by searching AND matching (exactly) all three Columns A-C in the "raw data" tab. I realize that VLOOKUP will only search the first Column in my Legends tab. Now I am at a complete loss how to achieve the desired outcome. Any ideas on how to do this would be really appreciated! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple col
I tested it and it worked... If you are in the Legends tab, you don't need to include that name in the formula... Make sure each value in A2, B2, C2 match exactly to the raw data column A, B and C values.. (check for spaces, spelling, format)... Also, make sure your ranges in the formula are large enough to cover all raw day rows. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109856 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple
Hi Shane:
Thank you so much for your time with this! It is numeric data I am trying to return. (right now the result is showing as zero). Do I need to change the --- in your formula below to the actual tab name? (with appropriate '! and all) No, there will not be more than one time that would match all three conditions. In some cases there will be no third condition, only two. (meaning the Column C in Raw Data would be blank). In the Legend Tab I need to have the number pulled from the Raw tab if Columns A-C have an identical match in the Raw tab. The number that needs to be retrieved is in the Raw tab (needs to be inserted into Column D in the Legend Tab. So for example: In Legend Tab: Column A - red Column B - blue Column C - yellow Then I need for the formula to go search in Raw tab to see the number of items that match all three of the above referenced criteria - in this case 9. Hope this helps more. "Shane Devenshire" wrote: Hi, What exactly are you trying to return? Is it numeric data or text? Do you want to count or sum the data? Can there be more than one item that matches all three conditions? Here is an example: =SUMPRODUCT(--(A$2:A$16=M1),--(B$2:B$16=M2),--(C$2:C$16=M3),D2:D16) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "MMangen" wrote: I have raw data that I put into a spreadsheet that I need to match to a pre-existing "legend". Legend Set-Up: Column A - Storefront Name Column B - Publisher Name Column C - Placement Name Column D - Orders I need to automatically insert data into Column D (Orders) by searching AND matching (exactly) all three Columns A-C in the "raw data" tab. I realize that VLOOKUP will only search the first Column in my Legends tab. Now I am at a complete loss how to achieve the desired outcome. Any ideas on how to do this would be really appreciated! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple col
Note: My formula only returns the first occurance of what might be in column D of the "Raw" tab... Use Sumproduct to get a count of matching entries... =SUMPRODUCT(--('Raw Data'!A$2:A$100=A2),--('Raw Data'!B$2:B$100=B2),--('Raw Data'!C$2:C$100)) Note that you have to adjust ranges to suit your raw data.. make sure the sheetname in the formula matches actual sheetname and that matches are exact in the 3 columns to your variables.. again check spelling, spaces, etc.. and that there are no errors in any of those columns already.... -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109856 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple
Thanks again, I did double check everything and everything is identical
(copied from one to the other for super duper certainty!) in Columns A-C on both tabs. Still no success. Michelle "NBVC" wrote: I tested it and it worked... If you are in the Legends tab, you don't need to include that name in the formula... Make sure each value in A2, B2, C2 match exactly to the raw data column A, B and C values.. (check for spaces, spelling, format)... Also, make sure your ranges in the formula are large enough to cover all raw day rows. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109856 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple col
Review Post #8 above. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109856 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple
Luke:
Actually, I was wrong, this formula is working EXCEPT I found there are instances of multiple lines having matching criteria and the formula isn't picking up all instances. So: for example: if there are three lines that all say Luke and you ordered one item three separate times. It only tells me you ordered one, instead of three. Any ideas on how to get a formula to pick up that last part? "MMangen" wrote: Luke: Thank you so much for taking your time to respond to my question. I am using Excel 2007 and trying to return a number. I may not have been entirely clear in my original question (I am getting a Value error right now): In the Legend Tab I need to have the number pulled from the Raw tab if Columns A-C have an identical match in the Raw tab. The number that needs to be retrieved is in the Raw tab (needs to be inserted into Column D in the Legend Tab. So for example: In Legend Tab: Column A - red Column B - blue Column C - yellow Then I need for the formula to go search in Raw tab to see the number of items that match all three of the above referenced criteria - in this case 9. Hope this helps more. Michelle "Luke M" wrote: You can use SUMPRODUCT If returning numbers: =SUMPRODUCT(('Raw Data'!A$2:A$100=A2)*('Raw Data'!B$2:B$100=B2)*('Raw Data'!C$2:C$100)*('Raw Data'!D$2:D$100)) If you're trying to return text: =INDEX('Raw Data'!D:D,SUMPRODUCT(('Raw Data'!A$2:A$100=A2)*('Raw Data'!B$2:B$100=B2)*('Raw Data'!C$2:C$100)*(ROW('Raw Data'!A$2:A$100)))) Adjust range sizes as appropriate. Note that you can't callout the entire column inside the SUMPRODUCT function unless you have XL 2007. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MMangen" wrote: I have raw data that I put into a spreadsheet that I need to match to a pre-existing "legend". Legend Set-Up: Column A - Storefront Name Column B - Publisher Name Column C - Placement Name Column D - Orders I need to automatically insert data into Column D (Orders) by searching AND matching (exactly) all three Columns A-C in the "raw data" tab. I realize that VLOOKUP will only search the first Column in my Legends tab. Now I am at a complete loss how to achieve the desired outcome. Any ideas on how to do this would be really appreciated! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUp or other function to return matched data from multiple col
Again, see post 8 above... SUMPRODUCT syntax he =Sumproduct(--(CriteriaRange1=Criteria1),--(CriteriaRange2=Criteria2),SumRange) where you can additional --(CriteriaRangeX=CriteriaX) -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109856 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching 2 list of data array, and return it as True if matched | Excel Discussion (Misc queries) | |||
Return matched value 2 criteria | Excel Worksheet Functions | |||
vlookup - return multiple data | Excel Discussion (Misc queries) | |||
VLookUp function to return multiple rows | Excel Worksheet Functions | |||
Return Title to matched column | New Users to Excel |