search value on base of 2 criteria

A

anurag.d

Here is my problem.
I want to make a search on 2 criterias.Vlookup can be used only for one
criteria.My data is structured this way:

Account Code Cost Centre Amount
112900 611200 5000
113100 611210 10000
114200 611200 12000

However,the cost centre is not always unique as has been shown in above
example but the account code is always unique.

I want to retrieve the third column i.e amount on the basis of account
code and cost centre.

Thanks!
 
M

Max

One way:

Assuming your sample data is in Sheet1,
cols A to C, data from row2 down
Account Code Cost Centre Amount
112900 611200 5000
113100 611210 10000
114200 611200 12000
Click to expand...

Put in D2: =A2&B2
Copy D2 down

In Sheet2
-----------
Assume same col structure as Sheet1, viz.:

Account Code Cost Centre Amount

where col C (Amount) is to be filled
based on "Account Code" and "Cost Centre"

Put in C2: =OFFSET(Sheet1!$A$1,MATCH(A2&B2,Sheet1!D:D,0)-1,2)
Copy C2 down

--
Alternatively, with an error trap for unmatched cases:

Put in C2:

=IF(ISNA(MATCH(A2&B2,Sheet1!D:D,0)),"No
match",OFFSET(Sheet1!$A$1,MATCH(A2&B2,Sheet1!D:D,0)-1,2))

where col C will return "No match" for unmatched cases instead of #NA
 
Top