I have the following lookup formula. It works fine except when the lookup
values are the same, it produces similar results when it should actually
produce another value.

{=INDEX(DATA!$AA$2:$AA$1018,MATCH(1,(DATA!$G$2:$G$ 1018=B29)*(DATA!$R$2:$R$1018=C29),0))}

A B C
1 abc def 123
2 abc def 456

In my data, it sometimes create the above example and thus, the formula
produce the result 123 twice, when it should be 123, followed by 456.

Is there anyone who can help me on this? Thanks in advance

One potent pair of simple formulae that delivers all of the duplicate
extractions
Your source data as posted is assumed in A2:C2 down
In E2: =IF(AND($A$2:$A$100="abc")*($B$2:$B$100="def"),ROW (),"")
In F2: =IF(ROWS($1:1)COUNT(E:E),"",INDEX(C:C,SMALL(E:E,R OWS($1:1))))
Copy E2:F2 down to cover the max expected extent of source data, say down to
F100? Minimize col E. Col F returns the required results. arrive`? celebrato,
hit the YES below
--
Max
Singapore
---
":)" wrote:
I have the following lookup formula. It works fine except when the lookup
values are the same, it produces similar results when it should actually
produce another value.

{=INDEX(DATA!$AA$2:$AA$1018,MATCH(1,(DATA!$G$2:$G$ 1018=B29)*(DATA!$R$2:$R$1018=C29),0))}

A B C
1 abc def 123
2 abc def 456

In my data, it sometimes create the above example and thus, the formula
produce the result 123 twice, when it should be 123, followed by 456.

Is there anyone who can help me on this? Thanks in advance

Hi,

Try this ARRAY formula

=INDEX(Data!$AA$2:$AA$1018,SMALL(IF(Data!$G$2:$G$1 018&Data!$R$2:$R$1018=B29&C29,ROW($A$2:$A$1018)-ROW($E$2)+1),ROWS(B$1:B1)))

When entered as an ARRAY it will return the first match, Drag down 1 row for
the second etc

Note that because this bit B29&C29 isn't absolute when you drag down it will
increment and be looking at B30&C30 so it should probably be absolute or
have the same lookup values in B30& C30

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


":)" wrote:

I have the following lookup formula. It works fine except when the lookup
values are the same, it produces similar results when it should actually
produce another value.

{=INDEX(DATA!$AA$2:$AA$1018,MATCH(1,(DATA!$G$2:$G$ 1018=B29)*(DATA!$R$2:$R$1018=C29),0))}

A B C
1 abc def 123
2 abc def 456

In my data, it sometimes create the above example and thus, the formula
produce the result 123 twice, when it should be 123, followed by 456.

Is there anyone who can help me on this? Thanks in advance

For a working solution, see:
http://lounge.windowssecrets.com/ind...owtopic=771787

--
Cheers
macropod
[Microsoft MVP - Word]


":)" wrote in message ...
I have the following lookup formula. It works fine except when the lookup
values are the same, it produces similar results when it should actually
produce another value.

{=INDEX(DATA!$AA$2:$AA$1018,MATCH(1,(DATA!$G$2:$G$ 1018=B29)*(DATA!$R$2:$R$1018=C29),0))}

A B C
1 abc def 123
2 abc def 456

In my data, it sometimes create the above example and thus, the formula
produce the result 123 twice, when it should be 123, followed by 456.

Is there anyone who can help me on this? Thanks in advance

Hi Max and Mike,

Thanks for your response. I tried both your method but did not really
achieve what I need though both solutions are fantastic. Correct me if I am
wrong, does your solution work if the lookup value happens to be volatile?
My previous example is a poor one and the example below will be more accurate
with the actual cells location and inputs.

B C D
28 Company Services Revenue
29 Bank IT
30 Bank IT
31 Retail Finance
32 Retail IT
33 F&B HR
33
34
35
36

I need a formula in D column so that it will match column B and C against my
database. In addition, cells in B and C are formula:
Column B
=IF(ISERROR(INDEX(DATA!G:G,IF(ROWS($1:1)COUNT(Wor king5),"",SMALL(Working5,ROWS($1:1))))),"",INDEX(D ATA!G:G,IF(ROWS($1:1)COUNT(Working5),"",SMALL(Wor king5,ROWS($1:1)))))

Column C
=IF(ISERROR(INDEX(DATA!R:R,IF(ROWS($1:1)COUNT(Wor king5),"",SMALL(Working5,ROWS($1:1))))),"",INDEX(D ATA!R:R,IF(ROWS($1:1)COUNT(Working5),"",SMALL(Wor king5,ROWS($1:1)))))

My current formula in D is
{=INDEX(DATA!$AA$2:$AA$1018,MATCH(1,(DATA!$G$2:$G$ 1018=B29)*(DATA!$R$2:$R$1018=C29),0))}

My formula in D is fine except the result in D29 and D30 should be different
as shown in my database.

Mike solution throws up #NUM! in D31 and D32. Is it because the lookup value
has change?






"Mike H" wrote:

Hi,

Try this ARRAY formula

=INDEX(Data!$AA$2:$AA$1018,SMALL(IF(Data!$G$2:$G$1 018&Data!$R$2:$R$1018=B29&C29,ROW($A$2:$A$1018)-ROW($E$2)+1),ROWS(B$1:B1)))

When entered as an ARRAY it will return the first match, Drag down 1 row for
the second etc

Note that because this bit B29&C29 isn't absolute when you drag down it will
increment and be looking at B30&C30 so it should probably be absolute or
have the same lookup values in B30& C30

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


":)" wrote:

I have the following lookup formula. It works fine except when the lookup
values are the same, it produces similar results when it should actually
produce another value.

{=INDEX(DATA!$AA$2:$AA$1018,MATCH(1,(DATA!$G$2:$G$ 1018=B29)*(DATA!$R$2:$R$1018=C29),0))}

A B C
1 abc def 123
2 abc def 456

In my data, it sometimes create the above example and thus, the formula
produce the result 123 twice, when it should be 123, followed by 456.

Is there anyone who can help me on this? Thanks in advance

My apologies, there was an error in the criteria formula for col E earlier
It should have read as:
In E2: =IF(AND(A2="abc",B2="def"),ROW(),"")
Then in F2:
=IF(ROWS($1:1)COUNT(E:E),"",INDEX(C:C,SMALL(E:E,R OWS($1:1))))
No change for the "extract-n-float it up" formula in F2. There, above is now
good to go, and it'll work easily as advertised when you copy both E2:F2 down
all the way. I've used this type of construct many, many times over w/o
incident. It's intuitively simple to change the criteria col E to suit
whatever other, more complex criteria that may be contemplated (you know
what's happening). Give it another go, let me know here.
--
Max
Singapore
---

Hi Max, I tried your earlier solution and your amended solution. Both worked
with similar results and did not came across the error that you mentioned.
Your solutions worked in my another issue but not for this.

B C D
28 Company Services Revenue
29 Bank IT xx
30 Bank IT xx
31 Retail Finance xx
32 Retail IT xx
33 F&B HR xx
33

I need results in column D 'XX' where data in column B and C might change
(increase/decrease), depending on my data source in another sheet and the
cells in B and C are formula driven:

Column B
=IF(ISERROR(INDEX(DATA!G:G,IF(ROWS($1:1)COUNT(Wor king5),"",SMALL(Working5,ROWS($1:1))))),"",INDEX(D ATA!G:G,IF(ROWS($1:1)COUNT(Working5),"",SMALL(Wor king5,ROWS($1:1)))))

Column C
=IF(ISERROR(INDEX(DATA!R:R,IF(ROWS($1:1)COUNT(Wor king5),"",SMALL(Working5,ROWS($1:1))))),"",INDEX(D ATA!R:R,IF(ROWS($1:1)COUNT(Working5),"",SMALL(Wor king5,ROWS($1:1)))))

As my lookup value might change, instead of setting as ="abc" and ="def", I
need to refer it to cells. So that when I drag the solution formula down, it
will lookup the new value against my source.

Mike solution
=INDEX(Data!$AA$2:$AA$1018,SMALL(IF(Data!$G$2:$G$1 018&Data!$R$2:$R$1018=B29&C29,ROW($A$2:$A$1018)-ROW($E$2)+1),ROWS(B$1:B1)))solved
my first 2 rows in the example as in my database, there are 2 entries with
bank and IT. However as my next row are referrence to retail/finance, it
produce #NUM!. After looking at the formula, I realise that it is because of
the last portion ROWS(B$1:B1) which is incremental.

My initial formula works
{=INDEX(DATA!$AA$2:$AA$1018,MATCH(1,(DATA!$G$2:$G$ 1018=B29)*(DATA!$R$2:$R$1018=C29),0))}
but was flawed if it happens that there are 2 entries with same company and
service as in my above example row 1 and 2.

Is there a formula that incorporate my initial formula and Mike so that in
normal scenario, it will pick up values but where there are reference values
that are duplicates, it is able to identify them and subsequently pick the
next data down the row in the source database? Another question that I have
is my source might have a company name but without services and this happen
often, thus my column B and C list only those which has a company name and
services as opposed to my source.


"Max" wrote:

My apologies, there was an error in the criteria formula for col E earlier
It should have read as:
In E2: =IF(AND(A2="abc",B2="def"),ROW(),"")
Then in F2:
=IF(ROWS($1:1)COUNT(E:E),"",INDEX(C:C,SMALL(E:E,R OWS($1:1))))
No change for the "extract-n-float it up" formula in F2. There, above is now
good to go, and it'll work easily as advertised when you copy both E2:F2 down
all the way. I've used this type of construct many, many times over w/o
incident. It's intuitively simple to change the criteria col E to suit
whatever other, more complex criteria that may be contemplated (you know
what's happening). Give it another go, let me know here.
--
Max
Singapore
---

As my lookup value might change, instead of setting as ="abc" and ="def", I
need to refer it to cells.

The above is certainly not an issue, just point the criteria to the 2 input
cells designated, and "fix" the points with $signs.

Assuming the input cells to be A1, B1
then in E2, copied down: =IF(AND(A2=$A$1,B2=$B$1),ROW(),"")
No change to F2

Why don't you just try getting the above up and working on a separate output
sheet? Both formulas in E2, F2 do NOT need array-entry.
--
Max
Singapore
---