Opposite of Vlookup

[Guest]

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.

 

[Bob Phillips]

=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)

 

[www.exciter.gr: Custom Excel Applications!]

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!

 

[Guest]

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

=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)

 

[Guest]

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.

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!

 

[Lars-Åke Aspelin]

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.

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!

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

 

[Lars-Åke Aspelin]

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