Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MoonWeazel
 
Posts: n/a
Default 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   Report Post  
swatsp0p
 
Posts: n/a
Default 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   Report Post  
MoonWeazel
 
Posts: n/a
Default 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   Report Post  
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
MoonWeazel
 
Posts: n/a
Default 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   Report Post  
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match Index cjjoo Excel Worksheet Functions 3 October 25th 05 09:33 AM
Match or Index Question carl Excel Worksheet Functions 2 October 4th 05 09:11 PM
Match & Index Phyllis B. Excel Worksheet Functions 2 November 27th 04 04:26 PM
Find a match that;s not exact Phyllis Excel Worksheet Functions 0 November 8th 04 09:12 PM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 07:11 PM


All times are GMT +1. The time now is 11:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"