2 options to try: via sumproduct & index/match
Illustrated in this sample:
http://www.savefile.com/files/1294895
Matching against multiple cols.xls
Matching against multiple cols.
Source data assumed in sheet: Data, cols A to D, data from row2 down
where cols A to C contain cheque nos, col D = Loan Agr nos
In another sheet,
Assume cheque numbers will be input in A2 down
Option 1. Using sumproduct
Put in B2:
=SUMPRODUCT((Data!$A$2:$C$100=A2)*Data!$D$2:$D$100 )
Copy down to return required results. This presumes that all cheque nos are
unique and the return col D, ie the loan agreement nos, are numbers. It fails
if there are duplicate cheque nos/text in return col.
Option 2. Using index/match
Put in C2:
=IF(ISNA(MATCH(A2,Data!A:A,0)),
IF(ISNA(MATCH(A2,Data!B:B,0)),
IF(ISNA(MATCH(A2,Data!C:C,0)),"",
INDEX(Data!D:D,MATCH(A2,Data!C:C,0))),
INDEX(Data!D:D,MATCH(A2,Data!B:B,0))),
INDEX(Data!D:D,MATCH(A2,Data!A:A,0)))
Copy down to return required results. If there are duplicate cheque nos,
it'll return the result based on the 1st match found. The matching sequence
across cols is col A col B col C. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Safi." wrote:
Hi,
Brief :
I have to enter cheque no.and in control find to find in say my data to get
the Loan Agreement No. the problem is that it. I do not have any record of
how many cheque I got or I entered on that date.
I want to enter cheque no. in a new sheet in column A and Can I get the
Agreement No. of the cust in the column B ?.. from my data sheet which has 3
cheques no in three columns if matched with among any three (1x3 Array)..
further the cheques no. are in three columns that can be an array to search
and get the next column value if my entry matches with any one among the
three.
Thanks in advance
Data Sheet
Chq No.1 Chq No.2 Chq No.3
Loan Agr No
3423 2568 2586
785462
6254 8452 4872
100285
Thanks,
Safi.