vlookup returns na
 

Hi all!

Sheet1 contains the following:
Col_H Col_O
302 33937
158 32840
3582 73345
39760 73455
4412 AGL92
2941 AHW92
136 30044

Sheet2 contains this:
Col_H
34132
34332
33532
31032
34433
34633
33937

This is the function I use along Col_I:
=IF(ISNA(VLOOKUP(H1;Sheet1!$H$1:$O
$7;1;FALSE))=TRUE;VLOOKUP(VALUE(H1);Sheet1!$H$1:$O
$7;1;FALSE);VLOOKUP(H1;Sheet!$H$1:$O$7;1;FALSE))

For H7 I would expect the function to return 302, but it only retuns
#N/A for all rows in Col_H.

I use concatenate to generate the data in Col_O in Sheet1. I also
tried to copy the values and do the vlookup inside the same sheet. But
still no result.

Please, advise!
WBR
Thommes

vlookup returns na
 

Try

=IF(ISNUMBER(MATCH(H7;Sheet1!$O$1:$O$7;0));INDEX(S heet1!$H$1:$H$7;MATCH(H7;Sheet1!$O$1:$O$7;0)))

--

HTH

Bob

"thommes" wrote in message
...
Hi all!

Sheet1 contains the following:
Col_H Col_O
302 33937
158 32840
3582 73345
39760 73455
4412 AGL92
2941 AHW92
136 30044

Sheet2 contains this:
Col_H
34132
34332
33532
31032
34433
34633
33937

This is the function I use along Col_I:
=IF(ISNA(VLOOKUP(H1;Sheet1!$H$1:$O
$7;1;FALSE))=TRUE;VLOOKUP(VALUE(H1);Sheet1!$H$1:$O
$7;1;FALSE);VLOOKUP(H1;Sheet!$H$1:$O$7;1;FALSE))

For H7 I would expect the function to return 302, but it only retuns
#N/A for all rows in Col_H.

I use concatenate to generate the data in Col_O in Sheet1. I also
tried to copy the values and do the vlookup inside the same sheet. But
still no result.

Please, advise!
WBR
Thommes

vlookup returns na
 

On 2 Apr., 11:37, "Bob Phillips" wrote:
Try

=IF(ISNUMBER(MATCH(H7;Sheet1!$O$1:$O$7;0));INDEX(S heet1!$H$1:$H$7;MATCH(H7;*Sheet1!$O$1:$O$7;0)))

--

HTH

Bob


Hi Bob!

Thank you. I tried it but it didn't work. Here is what I did:

302 33937 33937 #N/A FALSE
158 32840 32840 #N/A FALSE
3582 73345 73345 #N/A FALSE
39760 73455 73455 #N/A FALSE
4412 AGL92 AGL92 #VALUE! FALSE
2941 AHW92 AHW92 #VALUE! FALSE
136 30044 30044 #N/A FALSE

Cols A, B, E, F, G
In G I have your function: =IF(ISNUMBER(MATCH(E1;$A$1:$B$7;0));INDEX($A
$1:$B$7;MATCH(E1;$A$1:$B$7;0)))
which gives FALSE.

Any other ideas?

Best regards
Thommes

vlookup returns na
 

Hi,

VLOOKUP looks up the first column and returns a column to the right, It
can't work the other way around so try this

=IF(COUNTIF(Sheet1!$O$1:$O$7,H1)=0,"",INDEX(Sheet1 !$H$1:$H$7,MATCH(H1,Sheet1!$O$1:$O$7,0)))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"thommes" wrote:

Hi all!

Sheet1 contains the following:
Col_H Col_O
302 33937
158 32840
3582 73345
39760 73455
4412 AGL92
2941 AHW92
136 30044

Sheet2 contains this:
Col_H
34132
34332
33532
31032
34433
34633
33937

This is the function I use along Col_I:
=IF(ISNA(VLOOKUP(H1;Sheet1!$H$1:$O
$7;1;FALSE))=TRUE;VLOOKUP(VALUE(H1);Sheet1!$H$1:$O
$7;1;FALSE);VLOOKUP(H1;Sheet!$H$1:$O$7;1;FALSE))

For H7 I would expect the function to return 302, but it only retuns
#N/A for all rows in Col_H.

I use concatenate to generate the data in Col_O in Sheet1. I also
tried to copy the values and do the vlookup inside the same sheet. But
still no result.

Please, advise!
WBR
Thommes
.

vlookup returns na
 

Thommes,

I recreated the data as you laid out, and the first 6 gave me FALSE as yours
did, but I got 302 for the 7th as that is the only one matching.

I am not sure what all of those extra bits in your latest post mean.

--

HTH

Bob

"thommes" wrote in message
...
On 2 Apr., 11:37, "Bob Phillips" wrote:
Try

=IF(ISNUMBER(MATCH(H7;Sheet1!$O$1:$O$7;0));INDEX(S heet1!$H$1:$H$7;MATCH(H7;*Sheet1!$O$1:$O$7;0)))

--

HTH

Bob


Hi Bob!

Thank you. I tried it but it didn't work. Here is what I did:

302 33937 33937 #N/A FALSE
158 32840 32840 #N/A FALSE
3582 73345 73345 #N/A FALSE
39760 73455 73455 #N/A FALSE
4412 AGL92 AGL92 #VALUE! FALSE
2941 AHW92 AHW92 #VALUE! FALSE
136 30044 30044 #N/A FALSE

Cols A, B, E, F, G
In G I have your function: =IF(ISNUMBER(MATCH(E1;$A$1:$B$7;0));INDEX($A
$1:$B$7;MATCH(E1;$A$1:$B$7;0)))
which gives FALSE.

Any other ideas?

Best regards
Thommes

vlookup returns na
 

I forgot your regional setting uses ; and not , so try this


=IF(COUNTIF(Sheet1!$O$1:$O$7;H1)=0;"";INDEX(Sheet1 !$H$1:$H$7;MATCH(H1;Sheet1!$O$1:$O$7;0)))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

VLOOKUP looks up the first column and returns a column to the right, It
can't work the other way around so try this

=IF(COUNTIF(Sheet1!$O$1:$O$7,H1)=0,"",INDEX(Sheet1 !$H$1:$H$7,MATCH(H1,Sheet1!$O$1:$O$7,0)))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"thommes" wrote:

Hi all!

Sheet1 contains the following:
Col_H Col_O
302 33937
158 32840
3582 73345
39760 73455
4412 AGL92
2941 AHW92
136 30044

Sheet2 contains this:
Col_H
34132
34332
33532
31032
34433
34633
33937

This is the function I use along Col_I:
=IF(ISNA(VLOOKUP(H1;Sheet1!$H$1:$O
$7;1;FALSE))=TRUE;VLOOKUP(VALUE(H1);Sheet1!$H$1:$O
$7;1;FALSE);VLOOKUP(H1;Sheet!$H$1:$O$7;1;FALSE))

For H7 I would expect the function to return 302, but it only retuns
#N/A for all rows in Col_H.

I use concatenate to generate the data in Col_O in Sheet1. I also
tried to copy the values and do the vlookup inside the same sheet. But
still no result.

Please, advise!
WBR
Thommes
.

vlookup returns na
 

Hi again!

It all works perfectly well.

This is the function I used:
=IF(ISNA(VLOOKUP(H2;CYBEX_Bestand!$O$10:$P
$11392;2;FALSE))=TRUE;VLOOKUP(VALUE(H2);CYBEX_Best and!$O$10:$P
$11392;2;FALSE);VLOOKUP(H2;CYBEX_Bestand!$O$10:$P$ 11392;2;FALSE))

I copied the columns with the check values to the right according to
what Mike said:
"VLOOKUP looks up the first column and returns a column to the right,
It
can't work the other way around [...]"

Thanks to both of you, Bob and Mike!

Best regards

Thommes