Hi everybody !
Is there a a function in Excel that does something opposite to Vlookup. I
have a table that I want to compare with a data set in a column and find the
items that are not matching with those data.
--
Best regards,
Edward

=ISNA(MATCH(lookup_value, lookup_column,0))

will give True if not matched

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Edward" wrote in message
...
Hi everybody !
Is there a a function in Excel that does something opposite to Vlookup. I
have a table that I want to compare with a data set in a column and find
the
items that are not matching with those data.
--
Best regards,
Edward

You could try something like this:

=IF(ISERROR(VLOOKUP(D1,A:A;1,0)),"Not found","")

Best.
http://www.exciter.gr
Custom Excel Applications and Functions!


On Nov 3, 12:13 am, Edward wrote:
Hi everybody !
Is there a a function in Excel that does something opposite to Vlookup. I
have a table that I want to compare with a data set in a column and find the
items that are not matching with those data.
--
Best regards,
Edward

thanks Bob but I guess I need to explain more about the problem I have
A B F H
1 X 2 | X
2 Y 5 |== Z
3 Z T
4 T
5 S

As you can see I want to populate the H column with data in my table( column
A,B ) with a condition that if the entry in A matches any entry in F
disregard it otherwise populate column H . I hope I was clear .
--
Best regards,
Edward


"Bob Phillips" wrote:

=ISNA(MATCH(lookup_value, lookup_column,0))

will give True if not matched

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Edward" wrote in message
...
Hi everybody !
Is there a a function in Excel that does something opposite to Vlookup. I
have a table that I want to compare with a data set in a column and find
the
items that are not matching with those data.
--
Best regards,
Edward

Hi,
A B C F
1 X 2 X
2 Y 5
3 Z Z
4 T T
5 S

This isn't completely what you want because the acceptable values are on the
same rows as the source (X to X, Y to blank, Z to Z, etc.). However, the
formula is
=IF(ISNA(VLOOKUP(A2,C$2:C$6,1,FALSE)),B2,"")
The formula treats column C as the lookup range. If the value in column A
isn't in the lookup range, the lookup returns a N/A and the formula gets the
value in the same row, column B.


"www.exciter.gr: Custom Excel Application" wrote:

You could try something like this:

=IF(ISERROR(VLOOKUP(D1,A:A;1,0)),"Not found","")

Best.
http://www.exciter.gr
Custom Excel Applications and Functions!


On Nov 3, 12:13 am, Edward wrote:
Hi everybody !
Is there a a function in Excel that does something opposite to Vlookup. I
have a table that I want to compare with a data set in a column and find the
items that are not matching with those data.
--
Best regards,
Edward

On Fri, 2 Nov 2007 16:31:00 -0700, SoCal Rick
wrote:

Hi,
A B C F
1 X 2 X
2 Y 5
3 Z Z
4 T T
5 S

This isn't completely what you want because the acceptable values are on the
same rows as the source (X to X, Y to blank, Z to Z, etc.). However, the
formula is
=IF(ISNA(VLOOKUP(A2,C$2:C$6,1,FALSE)),B2,"")
The formula treats column C as the lookup range. If the value in column A
isn't in the lookup range, the lookup returns a N/A and the formula gets the
value in the same row, column B.


"www.exciter.gr: Custom Excel Application" wrote:

You could try something like this:

=IF(ISERROR(VLOOKUP(D1,A:A;1,0)),"Not found","")

Best.
http://www.exciter.gr
Custom Excel Applications and Functions!


On Nov 3, 12:13 am, Edward wrote:
Hi everybody !
Is there a a function in Excel that does something opposite to Vlookup. I
have a table that I want to compare with a data set in a column and find the
items that are not matching with those data.
--
Best regards,
Edward




Assuming that the G column can be used as a "helper column", try the
following:
(If G is not possible to use you can use some other free column)

- Clear cell G1
- In cell G2 put =G1+ISNA(VLOOKUP(A2;F:F;1;FALSE)
- Copy down column G as far as you need
- In cell H2 put =INDEX(B:B;MATCH(ROW().-1;G:G;0))
- Copy down column H as far as you need

The result will be the result you want "packed" in the H column from
row 2 and downwards.

Hope this helps / Lars-Åke

On Sat, 03 Nov 2007 17:41:49 GMT, Lars-Åke Aspelin
wrote:

On Fri, 2 Nov 2007 16:31:00 -0700, SoCal Rick
wrote:

Hi,
A B C F
1 X 2 X
2 Y 5
3 Z Z
4 T T
5 S

This isn't completely what you want because the acceptable values are on the
same rows as the source (X to X, Y to blank, Z to Z, etc.). However, the
formula is
=IF(ISNA(VLOOKUP(A2,C$2:C$6,1,FALSE)),B2,"")
The formula treats column C as the lookup range. If the value in column A
isn't in the lookup range, the lookup returns a N/A and the formula gets the
value in the same row, column B.


"www.exciter.gr: Custom Excel Application" wrote:

You could try something like this:

=IF(ISERROR(VLOOKUP(D1,A:A;1,0)),"Not found","")

Best.
http://www.exciter.gr
Custom Excel Applications and Functions!


On Nov 3, 12:13 am, Edward wrote:
Hi everybody !
Is there a a function in Excel that does something opposite to Vlookup. I
have a table that I want to compare with a data set in a column and find the
items that are not matching with those data.
--
Best regards,
Edward




Assuming that the G column can be used as a "helper column", try the
following:
(If G is not possible to use you can use some other free column)

- Clear cell G1
- In cell G2 put =G1+ISNA(VLOOKUP(A2;F:F;1;FALSE)
- Copy down column G as far as you need
- In cell H2 put =INDEX(B:B;MATCH(ROW().-1;G:G;0))
- Copy down column H as far as you need

The result will be the result you want "packed" in the H column from
row 2 and downwards.

Hope this helps / Lars-Åke

Ooops, there was an "." in one of the formulas by mistake.
New try:

Assuming that the G column can be used as a "helper column", try the
following:
(If G is not possible to use you can use some other free column)

- Clear cell G1
- In cell G2 put =G1+ISNA(VLOOKUP(A2;F:F;1;FALSE)
- Copy down column G as far as you need
- In cell H2 put =INDEX(B:B;MATCH(ROW()-1;G:G;0))
- Copy down column H as far as you need

The result will be the result you want "packed" in the H column from
row 2 and downwards.

Hope this helps / Lars-Åke