Help Required

[Safi.]

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.

 

[ryguy7272]

I can't tell exactly what you are trying to do, but I think this will get you
close to where you want to be:
http://www.contextures.com/xlFunctions02.html

http://www.contextures.com/xlDataVal02.html

Happy New Year!!
Ryan--

 

[Max]

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,MATCH(A2,Data!C:C,0))),
INDEX(Data!D,MATCH(A2,Data!B:B,0))),
INDEX(Data!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.