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

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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>