vlookup returns na

T

thommes

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
 
B

Bob Phillips

Try

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

thommes

Try

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

--

HTH

Bob
Click to expand...

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
 
M

Mike H

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.
 
B

Bob Phillips

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

Try

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

--

HTH

Bob
Click to expand...

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
 
M

Mike H

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.
 
T

thommes

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,Sheet­1!$O$1:$O$7,0)))
Click to expand...
Hi Mike!

Sorry, I think the table was misleading. These are my data:
302 33937 1
158 32840 2
3582 73345 7
39760 73455 3000
4412 AGL92 abc
2941 AHW92 xyz
136 30044 33937 #N/A

Cols A, B, E and F.
Your function is now in Col F. I think this is very close to a
solution. Only row 7 gives #N/A. And it should yield 302. I'll try
some formatting first.

Thank you! :)

Regards
Thommes
 
T

thommes

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_Bestand!$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
 
Top