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