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
---