Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default EXCEL - Meet 2 criteria, then find next case of third criteria

I have been able to get excel to meet 2 criteria and then give me the value
found in a third cell located in another column and 5 cells down. Now I
would like excel to meet 2 criteria, then search below for the next criteria
and give me the value found in the cell beside.

i.e. H10 = XXXX, J10 = XXXXXXXX, now find next case of XXXXX in Column K and
give me the contents beside it in colum L.

To find the first set of criteria, I have used
=INDEX(L:L,MATCH(1,INDEX((H:H=XXXX)*(J:J=XXXXXXXXX ),),)+5). Can anyone help
me add to this to go to the next case of XXXYYY found in column K and then
give me the contents of the cell beside it in L ?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 913
Default EXCEL - Meet 2 criteria, then find next case of third criteria

On Sun, 30 Nov 2008 11:42:01 -0800, Elaine
wrote:

I have been able to get excel to meet 2 criteria and then give me the value
found in a third cell located in another column and 5 cells down. Now I
would like excel to meet 2 criteria, then search below for the next criteria
and give me the value found in the cell beside.

i.e. H10 = XXXX, J10 = XXXXXXXX, now find next case of XXXXX in Column K and
give me the contents beside it in colum L.

To find the first set of criteria, I have used
=INDEX(L:L,MATCH(1,INDEX((H:H=XXXX)*(J:J=XXXXXXXX X),),)+5). Can anyone help
me add to this to go to the next case of XXXYYY found in column K and then
give me the contents of the cell beside it in L ?


Try this formula:

=INDEX(OFFSET(L1,MATCH(1,INDEX((H:H=XXXX)*(J:J=XXX XXXXXX),),)-1,0,1000,),MATCH(XXXYYY,OFFSET(K1,MATCH(1,INDEX((H :H=XXXX)*(J:J=XXXXXXXXX),),)-1,0,1000,),0))

(all of the formula should be on one line)

The search for XXXYYY now starts on the row that matches XXXX and
XXXXXXXXX
If the search for XXXYYY should start on the row below that row, then
remove the -1 in two places in the formula.

Adapt the 1000, in two places, to match the size of you data table.
Instead of using H:H and J:J you could put J1:Jn and H1:Hn where n is
the size of your data. This makes the formula quicker to calculate.

Hope this helps / Lars-Åke
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default EXCEL - Meet 2 criteria, then find next case of third criteria

Thank you, however I am getting a #N/A. I neglected to mention that the
Match in the K column (the third criteria) is a text not numerical. Would
this make a difference ? It is "AID :". Does the colon cause a problem ?

"Lars-Ã…ke Aspelin" wrote:

On Sun, 30 Nov 2008 11:42:01 -0800, Elaine
wrote:

I have been able to get excel to meet 2 criteria and then give me the value
found in a third cell located in another column and 5 cells down. Now I
would like excel to meet 2 criteria, then search below for the next criteria
and give me the value found in the cell beside.

i.e. H10 = XXXX, J10 = XXXXXXXX, now find next case of XXXXX in Column K and
give me the contents beside it in colum L.

To find the first set of criteria, I have used
=INDEX(L:L,MATCH(1,INDEX((H:H=XXXX)*(J:J=XXXXXXXX X),),)+5). Can anyone help
me add to this to go to the next case of XXXYYY found in column K and then
give me the contents of the cell beside it in L ?


Try this formula:

=INDEX(OFFSET(L1,MATCH(1,INDEX((H:H=XXXX)*(J:J=XXX XXXXXX),),)-1,0,1000,),MATCH(XXXYYY,OFFSET(K1,MATCH(1,INDEX((H :H=XXXX)*(J:J=XXXXXXXXX),),)-1,0,1000,),0))

(all of the formula should be on one line)

The search for XXXYYY now starts on the row that matches XXXX and
XXXXXXXXX
If the search for XXXYYY should start on the row below that row, then
remove the -1 in two places in the formula.

Adapt the 1000, in two places, to match the size of you data table.
Instead of using H:H and J:J you could put J1:Jn and H1:Hn where n is
the size of your data. This makes the formula quicker to calculate.

Hope this helps / Lars-Ã…ke

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 913
Default EXCEL - Meet 2 criteria, then find next case of third criteria

Text should not make a difference. But you must have an exact match or
you will get the #N/A error.
Make sure that you don't have any leading or trailing or other blanks
that are in the string that you want to search for.
In your example you write "AID :", i.e. with a blank between D and :
That will not match "AID:", i.e. without any blanks.

Hope this helps / Lars-Åke

On Sun, 30 Nov 2008 19:26:01 -0800, Elaine
wrote:

Thank you, however I am getting a #N/A. I neglected to mention that the
Match in the K column (the third criteria) is a text not numerical. Would
this make a difference ? It is "AID :". Does the colon cause a problem ?

"Lars-Åke Aspelin" wrote:

On Sun, 30 Nov 2008 11:42:01 -0800, Elaine
wrote:

I have been able to get excel to meet 2 criteria and then give me the value
found in a third cell located in another column and 5 cells down. Now I
would like excel to meet 2 criteria, then search below for the next criteria
and give me the value found in the cell beside.

i.e. H10 = XXXX, J10 = XXXXXXXX, now find next case of XXXXX in Column K and
give me the contents beside it in colum L.

To find the first set of criteria, I have used
=INDEX(L:L,MATCH(1,INDEX((H:H=XXXX)*(J:J=XXXXXXXX X),),)+5). Can anyone help
me add to this to go to the next case of XXXYYY found in column K and then
give me the contents of the cell beside it in L ?


Try this formula:

=INDEX(OFFSET(L1,MATCH(1,INDEX((H:H=XXXX)*(J:J=XXX XXXXXX),),)-1,0,1000,),MATCH(XXXYYY,OFFSET(K1,MATCH(1,INDEX((H :H=XXXX)*(J:J=XXXXXXXXX),),)-1,0,1000,),0))

(all of the formula should be on one line)

The search for XXXYYY now starts on the row that matches XXXX and
XXXXXXXXX
If the search for XXXYYY should start on the row below that row, then
remove the -1 in two places in the formula.

Adapt the 1000, in two places, to match the size of you data table.
Instead of using H:H and J:J you could put J1:Jn and H1:Hn where n is
the size of your data. This makes the formula quicker to calculate.

Hope this helps / Lars-Åke


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
EXCEL FORMULA , MEET 2 CRITERIA THEN SUM Elaine Excel Worksheet Functions 6 November 26th 08 04:38 PM
how to get the median of cells that meet a given criteria in excel drs207 Excel Worksheet Functions 1 July 17th 08 07:26 PM
How to get Excel to Recalculate a formula if the answer does not meet criteria Al[_2_] Excel Worksheet Functions 2 March 26th 07 10:27 AM
How to copy rows that meet criteria to another sheet in Excel Bruce Excel Worksheet Functions 9 November 3rd 06 12:12 AM
Hide rows that meet certain criteria in an excel template SandyZapp Excel Worksheet Functions 0 July 5th 06 09:34 PM


All times are GMT +1. The time now is 08:51 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"