Do a vlookup based on left 6 numbers in the table

Try

=IF(SUMPRODUCT(--(LEFT(AR!$C$6:$C$100,6)=LEFT(D10,6))),"N","Y")

Sorry... changed post to Column L

Originally Posted by Special-K

Column K in the collections tab is "Net Amount Received" and contains figures.
Are you sure you want these figures overwritten with a Y/N ?

Try (untested)
=IF(ISNA(VLOOKUP(LEFT(D10,6),AR!C$6:C$1000,0)),"N","Y")

Sorry... changed post to Column L, but that formula did not work.

Originally Posted by :) Sixthsense :)

In Row 10 of Collections sheet

=SUM(COUNTIF(AR!C:C,INDEX(CHOOSE({1,2},--LEFT(D10),D10),,N(IF(1,{1,2})))))

Drag it down....

That put a $ - in the Column L

Originally Posted by Jonmo1

Try

=IF(SUMPRODUCT(--(LEFT(AR!$C$6:$C$100,6)=LEFT(D10,6))),"N","Y")

That worked! Can you explain it to me by chance?

Originally Posted by kafarrell

That worked! Can you explain it

You're welcome.

It compares the left 6 characters of D10 to the left 6 characters of all the values in C6:C100.
And it counts how many of them match.
If that count is 0, it returns Y, if it's greater than 0 it returns N

Originally Posted by :) Sixthsense :)

Oopss.... I Forgot to add an IF... Try this...

=IF(SUM(COUNTIF(AR!C:C,INDEX(CHOOSE({1,2},--LEFT(D10),D10),,N(IF(1,{1,2}))))),"N","Y")

That worked too! Thanks!

Originally Posted by Jonmo1

You're welcome.

It compares the left 6 characters of D10 to the left 6 characters of all the values in C6:C100.
And it counts how many of them match.
If that count is 0, it returns Y, if it's greater than 0 it returns N

What is the --?

-- Converts True/False values into 1/0 values
True = 1, False = 0

(LEFT(AR!$C$6:$C$100,6)=LEFT(D10,6))
This is an expression (or question) withe a True or False answer.
It reads 1 row at a time, and asks Is the left 6 characters from column C equal to the left 6 characters of D10? True or False.
It creates an array of all the True/False results of that question for each cell in C6:C100
The -- converts those True/False into 1/0
Then the sumproduct SUMS those 1's and 0's.
If that sum is 0, it returns "Y"
If that sum is any number greater than 0, it returns "N"

Originally Posted by Jonmo1

-- Converts True/False values into 1/0 values
True = 1, False = 0

(LEFT(AR!$C$6:$C$100,6)=LEFT(D10,6))
This is an expression (or question) withe a True or False answer.
It reads 1 row at a time, and asks Is the left 6 characters from column C equal to the left 6 characters of D10? True or False.
It creates an array of all the True/False results of that question for each cell in C6:C100
The -- converts those True/False into 1/0
Then the sumproduct SUMS those 1's and 0's.
If that sum is 0, it returns "Y"
If that sum is any number greater than 0, it returns "N"

Thanks so much! I'm learning... slowly, but surely.

Originally Posted by martindwilson

you could probably just use INT()

I considered that as well, but it will error if there are any text values in C6:C100...

It also assumes that all the numbers are 6 digits (not including the decimal value).

So if D10 was say 123456.7
And you had 12345678.9 in C20 or whatever, then it wouldn't be a match.
But it is a match using the left function.
But we don't know if that's desireable or not....

But not on actual Text like "hello".

Just forward thinking in case there are actual text strings mixed up in the range.