Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Required
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Required
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-- -- RyGuy "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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Required
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Required.... | Excel Discussion (Misc queries) | |||
Help Required | Excel Discussion (Misc queries) | |||
Help required | New Users to Excel | |||
Help required | Excel Discussion (Misc queries) | |||
Help required...... | Excel Discussion (Misc queries) |