| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match multiple values
I need to match 3 values as follows: B5 match with sheet1!A3:A194 C5 match with sheet1!B3:B194 D5 match with sheet1!C3:C194 Return value from column sheet1!H3:H194 W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
Hi,
Assume that your data in H3:H194 is numeric, you may use the following: =sumproduct((A3:A194=B5)*(B3:B194=C5)*(C3:C194=D5) *(H3:H194)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "willemeulen" wrote in message ... I need to match 3 values as follows: B5 match with sheet1!A3:A194 C5 match with sheet1!B3:B194 D5 match with sheet1!C3:C194 Return value from column sheet1!H3:H194 W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
If the data is not numeric, what would be the forumula resulting in
False/True. Example: Invoice# Line # Operator# 85687 1 1266 85725 1 1374 85725 2 1374 85725 3 1374 85725 4 1266 85757 1 2455 85757 2 2455 85757 3 1374 Thanks "Ashish Mathur" wrote in message ... Hi, Assume that your data in H3:H194 is numeric, you may use the following: =sumproduct((A3:A194=B5)*(B3:B194=C5)*(C3:C194=D5) *(H3:H194)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "willemeulen" wrote in message ... I need to match 3 values as follows: B5 match with sheet1!A3:A194 C5 match with sheet1!B3:B194 D5 match with sheet1!C3:C194 Return value from column sheet1!H3:H194 W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
Hi,
=and(A3=B5,B3=C5,C3=D5) and then copy down. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Krish" wrote in message ... If the data is not numeric, what would be the forumula resulting in False/True. Example: Invoice# Line # Operator# 85687 1 1266 85725 1 1374 85725 2 1374 85725 3 1374 85725 4 1266 85757 1 2455 85757 2 2455 85757 3 1374 Thanks "Ashish Mathur" wrote in message ... Hi, Assume that your data in H3:H194 is numeric, you may use the following: =sumproduct((A3:A194=B5)*(B3:B194=C5)*(C3:C194=D5) *(H3:H194)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "willemeulen" wrote in message ... I need to match 3 values as follows: B5 match with sheet1!A3:A194 C5 match with sheet1!B3:B194 D5 match with sheet1!C3:C194 Return value from column sheet1!H3:H194 W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
Unfortunately the following formula doesn't work: Quote "arthur" Assume that your data in H3:H194 is numeric, you may use the following: =sumproduct((A3:A194=B5)*(B3:B194=C5)*(C3:C194=D5) *(H3:H194)) I edited the formula to this as the lookup table is on another sheet, sheet1! =sumproduct((sheet1!A3:A194=B5)*(sheet1!B3:B194=C5 )*(Sheet1!C3:C194=D5) *(sheet1!H3:H194)) It returns #value!, most probably because values are not all numeric Have a look at the attached file, Sheet 1 shows the extracted data needed for the lookup (green) and the data sourced/updated when opened from internet (yellow), I need to extract the price from column H depending on my input value's on the compare sheet. W +-------------------------------------------------------------------+ |Filename: PIPE COST COMPARISON.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=164| +-------------------------------------------------------------------+ -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
In article ,
willemeulen wrote: I need to match 3 values as follows: B5 match with sheet1!A3:A194 C5 match with sheet1!B3:B194 D5 match with sheet1!C3:C194 Return value from column sheet1!H3:H194 W Try... =INDEX(Sheet1!H3:H194,MATCH(1,IF(Sheet1!A3:A194=B5 ,IF(Sheet1!B3:B194=C5,I F(Sheet1!C3:C194=D5,1))),0)) ....confirmed with CONTROL+SHIFT+ENTER. -- Domenic http://www.xl-central.com |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
It doesn't work because you have VALUE on the source sheet in hidden rows.
-- Don Guillett Microsoft MVP Excel SalesAid Software "willemeulen" wrote in message ... Unfortunately the following formula doesn't work: Quote "arthur" Assume that your data in H3:H194 is numeric, you may use the following: =sumproduct((A3:A194=B5)*(B3:B194=C5)*(C3:C194=D5) *(H3:H194)) I edited the formula to this as the lookup table is on another sheet, sheet1! =sumproduct((sheet1!A3:A194=B5)*(sheet1!B3:B194=C5 )*(Sheet1!C3:C194=D5) *(sheet1!H3:H194)) It returns #value!, most probably because values are not all numeric Have a look at the attached file, Sheet 1 shows the extracted data needed for the lookup (green) and the data sourced/updated when opened from internet (yellow), I need to extract the price from column H depending on my input value's on the compare sheet. W +-------------------------------------------------------------------+ |Filename: PIPE COST COMPARISON.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=164| +-------------------------------------------------------------------+ -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
That is how I get the stuff from the net, how can I change that? Material prices are split in classes on internet and give a header row each time, luckily hiding the rows does not affect the refreshing of data, how do I need to solve this, would unhiding rows help? -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
Whoever sent it to you needs to goto an Excel class.
Goto sheet1select the col b HEADERf5specialblanksOKentirerowthen touch the delete key Sub ClearBlanks()' this macro does the above Columns("B"). _ SpecialCells(xlCellTypeBlanks). _ EntireRow.Delete End Sub then put this.ONE line in cell f5 =SUMPRODUCT((Sheet1!$A$3:$A$154=B5)*(Sheet1!$B$3:$ B$154=C5)*(Sheet1!$C$3:$C$154=TEXT(D5,"###"))*Shee t1!$H$3:$H$154) -- Don Guillett Microsoft MVP Excel SalesAid Software "willemeulen" wrote in message ... That is how I get the stuff from the net, how can I change that? Material prices are split in classes on internet and give a header row each time, luckily hiding the rows does not affect the refreshing of data, how do I need to solve this, would unhiding rows help? -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
Don, Jerrr:nuts, you're making up some serious formula's/programming for my skills. Is it possible to modify the web query or am I stuck with how it comes in. If so it means I have to run a macro or similar (like you suggest) to keep things automated, my data are always freshly imported when I open the file. Let try what doctor "Guillett" prescribed and see where I get stuck. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
OK. I have looked at this some more and it appears that the only data that
comes from the net is in the yellow section (cols d:h). Is it always the SAME url? Then, it appears that YOU create the formulas that are causing the problems. I think you need a macro to massage your data to get rid of the blank rows and then have the macro create your formulas and your sumproduct formulas will work. If you can't do this then you need to have custom programming. All of this CAN be done with ONE mouse click. Contact me privately, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "willemeulen" wrote in message ... Don, Jerrr:nuts, you're making up some serious formula's/programming for my skills. Is it possible to modify the web query or am I stuck with how it comes in. If so it means I have to run a macro or similar (like you suggest) to keep things automated, my data are always freshly imported when I open the file. Let try what doctor "Guillett" prescribed and see where I get stuck. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
I looked at this again and a major modification to your col a:c formulas along with major mods to the sumproduct formula negate the need for macros. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... OK. I have looked at this some more and it appears that the only data that comes from the net is in the yellow section (cols d:h). Is it always the SAME url? Then, it appears that YOU create the formulas that are causing the problems. I think you need a macro to massage your data to get rid of the blank rows and then have the macro create your formulas and your sumproduct formulas will work. If you can't do this then you need to have custom programming. All of this CAN be done with ONE mouse click. Contact me privately, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "willemeulen" wrote in message ... Don, Jerrr:nuts, you're making up some serious formula's/programming for my skills. Is it possible to modify the web query or am I stuck with how it comes in. If so it means I have to run a macro or similar (like you suggest) to keep things automated, my data are always freshly imported when I open the file. Let try what doctor "Guillett" prescribed and see where I get stuck. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
willemeulen;376188 Wrote: I need to match 3 values as follows: B5 match with sheet1!A3:A194 C5 match with sheet1!B3:B194 D5 match with sheet1!C3:C194 Return value from column sheet1!H3:H194 W Try This INDEX(Sheet1!$H$3:$H$194,MATCH(COMPARE!B5&COMPARE! C5&COMPARE!D5,Sheet1!$A$3:$A$194&Sheet1!$B$3:$B$19 4&Sheet1!$C$3:$C$194,0)) Confirmed with CTRL+SHIFT+ENTER -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
This solution worked better than mine and did not require a lot of rewrite.
I would only remove the reference to compare! as the formula is on the compare sheet already. Don't forget to array enter. INDEX(Sheet1!$H$3:$H$194,MATCH(COMPARE!B5&COMPARE! C5&COMPARE!D5,Sheet1!$A$3:$A$194&Sheet1!$B$3:$B$19 4&Sheet1!$C$3:$C$194,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... I looked at this again and a major modification to your col a:c formulas along with major mods to the sumproduct formula negate the need for macros. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... OK. I have looked at this some more and it appears that the only data that comes from the net is in the yellow section (cols d:h). Is it always the SAME url? Then, it appears that YOU create the formulas that are causing the problems. I think you need a macro to massage your data to get rid of the blank rows and then have the macro create your formulas and your sumproduct formulas will work. If you can't do this then you need to have custom programming. All of this CAN be done with ONE mouse click. Contact me privately, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "willemeulen" wrote in message ... Don, Jerrr:nuts, you're making up some serious formula's/programming for my skills. Is it possible to modify the web query or am I stuck with how it comes in. If so it means I have to run a macro or similar (like you suggest) to keep things automated, my data are always freshly imported when I open the file. Let try what doctor "Guillett" prescribed and see where I get stuck. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
This took a while to get figured out, thanks guys for the efforts put in but the last solution suggested works. For some reason the sumproduct function did not want to work, even if I removed all formulas and only focussed on the the first section of the lookup collumn it refused to return the price, alsmost gave up on it. For me its still a mystery why it did not want to work while now it works with gaps and everything. Thanks guys. W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
| find multiple occurances of a value and match to values in another | Excel Discussion (Misc queries) | |||
| Returning MULTIPLE values with Index and Match | Excel Discussion (Misc queries) | |||
| How do I add multiple values that match multiple conditions? | Excel Discussion (Misc queries) | |||
| match/index using multiple values | Excel Worksheet Functions | |||
![willemeulen[_41_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
