Index and Match - the next step

[MoonWeazel]

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

 

[swatsp0p]

It would be most helpful if you posted the current formula that i
working, the current results and a sample of your desired results.

More info, please

 

[MoonWeazel]

hi bruce,

sorry about not posting too much! thought people might be confused!

the formula is:

=IF(ISERROR(INDEX(quarters,MATCH($A11,type,0),MATCH($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 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)