Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Index and Match - the next step
Afternoon All, I have a combined index match formula which is working fine! Col A Col B Col C Col D project a criteria x 10 20 project a criteria y 5 30 project b criteria x 20 50 project b criteria y 30 60 so the current index&match combo looks at col b and returns the figure in either col c or col d (depending on what you choose) for either criteria x or y (depending on what you choose). but as criteria x applies to both projects and i only want to return the value for criteria x for project b, how do i change the formula to take into consideration this extra value? i have had a look at using vlookup/sumproduct but can't seem to get it to work! i could do with another match argument in the index formula but can't get it to work either! thanks in advance for any help you can offer! (i hope my ramblings made sense!) cheers, moonweazel -- MoonWeazel ------------------------------------------------------------------------ MoonWeazel's Profile: http://www.excelforum.com/member.php...fo&userid=2119 View this thread: http://www.excelforum.com/showthread...hreadid=479424 |
#2
|
|||
|
|||
Index and Match - the next step
It would be most helpful if you posted the current formula that is working, the current results and a sample of your desired results. More info, please. -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=479424 |
#3
|
|||
|
|||
Index and Match - the next step
hi bruce, sorry about not posting too much! thought people might be confused! the formula is: =IF(ISERROR(INDEX(quarters,MATCH($A11,type,0),MATC H($I$4,quarter_list,0)))=TRUE,"£0",INDEX(quarters, MATCH($A11,type,0),MATCH($I$4,quarter_list,0))) the data source is pictured in the piccy. the named range 'quarters' is all the figures, 'type' is the column labelled 'type' and 'quarter_list' is the headings for the figures (1st q post exe etc). the other two cell refs mentioned are cells with list data validation in them, so you can choose which type and which quarter you want to look at. i would like to be able to choose the project name, the type and the quarter and have the correct value returned. for example: project number = 123456 type = claimed by BU quarter = 1st post exe value returned = £625 there is only one project listed (that was a fudge when i showed my boss so the calcs all tallyed!) there will be more projects added! i hope this makes sense! thanks, moonweazel +-------------------------------------------------------------------+ |Filename: Drawing1.png | |Download: http://www.excelforum.com/attachment.php?postid=3951 | +-------------------------------------------------------------------+ -- MoonWeazel ------------------------------------------------------------------------ MoonWeazel's Profile: http://www.excelforum.com/member.php...fo&userid=2119 View this thread: http://www.excelforum.com/showthread...hreadid=479424 |
#4
|
|||
|
|||
Index and Match - the next step
If you mean that you want to limit the lookup to just project b, then try
this =INDEX(C1:C100,MATCH("criteria x",IF(A1:A100="project b",B1:B100),0)) it is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "MoonWeazel" wrote in message ... Afternoon All, I have a combined index match formula which is working fine! Col A Col B Col C Col D project a criteria x 10 20 project a criteria y 5 30 project b criteria x 20 50 project b criteria y 30 60 so the current index&match combo looks at col b and returns the figure in either col c or col d (depending on what you choose) for either criteria x or y (depending on what you choose). but as criteria x applies to both projects and i only want to return the value for criteria x for project b, how do i change the formula to take into consideration this extra value? i have had a look at using vlookup/sumproduct but can't seem to get it to work! i could do with another match argument in the index formula but can't get it to work either! thanks in advance for any help you can offer! (i hope my ramblings made sense!) cheers, moonweazel -- MoonWeazel ------------------------------------------------------------------------ MoonWeazel's Profile: http://www.excelforum.com/member.php...fo&userid=2119 View this thread: http://www.excelforum.com/showthread...hreadid=479424 |
#5
|
|||
|
|||
Index and Match - the next step
hi bob, thanks for the help! i need to be able to work in another criteria! there is more information and a screen shot available in my second post! thanks anyway! moonweazel -- MoonWeazel ------------------------------------------------------------------------ MoonWeazel's Profile: http://www.excelforum.com/member.php...fo&userid=2119 View this thread: http://www.excelforum.com/showthread...hreadid=479424 |
#6
|
|||
|
|||
Index and Match - the next step
I can't get anything from that image I am afraid, but just add the extra
condition like this =INDEX(C1:C100,MATCH("criteria x",IF(((A1:A100="project b")*(C1:C100="something")),B1:B100),0)) it is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "MoonWeazel" wrote in message ... hi bob, thanks for the help! i need to be able to work in another criteria! there is more information and a screen shot available in my second post! thanks anyway! moonweazel -- MoonWeazel ------------------------------------------------------------------------ MoonWeazel's Profile: http://www.excelforum.com/member.php...fo&userid=2119 View this thread: http://www.excelforum.com/showthread...hreadid=479424 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |