Matching from a Range

Thanks, it has solved my problem.

FDibbins, sorry. But it is working only when the numbers are in ascending. It is not giving accurate result where the numbers are not in ascending order.

If your data is not Sort you can use this formulas

Please Login or Register  to view this content.

dhn46,

This formula is returning the last numbers of check book instead of name of the person which I want to know.

Originally Posted by chandan12

dhn46,

This formula is returning the last numbers of check book instead of name of the person which I want to know.

My formulas returning the value of brach column, dont you want this value? Sorry my English not good. Please see file, if wrong, can you up the example result.

I am not getting the same result. Look at my worksheet. I have just changed the check numbers in sheet "CHK" and out of 38 entry only 8 are giving correct result. Hope your guidance.

Originally Posted by chandan12

I am not getting the same result. Look at my worksheet. I have just changed the check numbers in sheet "CHK" and out of 38 entry only 8 are giving correct result. Hope your guidance.

Hi chandan12!
I compared the formula in #2 with fomula in #37 when the numbers are in ascending, so i didn't find the diferrence. Can you upload your File with the result was type by hand?

Please see attach file with my previous reply. The name is "my book".

bebo021999! I have tried with your help but getting "#NAME?" in all cells. I am using Office 2003.

You Try this formula

Please Login or Register  to view this content.

The end: Ctrl + Shift + Enter

dhn46

Getting "Not Available" for all and which is not true.

Originally Posted by chandan12

dhn46

Getting "Not Available" for all and which is not true.

Hi chandan12!
The formula in #15 is the Array fomula
You must Ctrl + Shift + Enter, not Enter
Please see file to view the result.

Originally Posted by dhn46

Hi chandan12!
The formula in #15 is the Array fomula
You must Ctrl + Shift + Enter, not Enter
Please see file to view the result.

Yes, got it.
Thanks.

Originally Posted by Lemice

Hello,

You can use this Array formula

Please Login or Register  to view this content.

Because it is an Array formula, you will need to confirm with Ctrl-Shift-Enter. If you see the formula being wrapped inside a { } then you did it right.

Here is your sample file with the formula on Column E.

Yes. Thanks. I have no idea about accepting an array formula in this way. My problem has solved.

I already marked the thread as solved. But the array function is taking time to load the file. Can we solve the problem without Array Function?

Originally Posted by Lemice

You can try this formula then

Please Login or Register  to view this content.

Enter it as normal. You don't have to enter it as array.

And also you should make a helper column to "erase" the #N/A instead of putting it into an ISERROR, because it will just make the formula calculating twice.

Thanks. I shall try it. Please note, the numbers of entry in BB sheet will increases day by day and it may go upto row 65000.

Originally Posted by chandan12

Thanks. I shall try it. Please note, the numbers of entry in BB sheet will increases day by day and it may go upto row 65000.

Hi chandan12
If the numbers of entry in BB sheet will increases day by day and it may go upto row 65000 you shouldn't use Fomula. I think VBA method is better method for this case. See my file, press Run button to view result.

dhn46

Could you tell me the ways to use it in VBA method.