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

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

"thommes" <thommes@c2i.net> wrote in message 
news:d13dc0f5-cdbd-4cfc-9784-719370adbfea@l36g2000yqb.googlegroups.com...
> 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 

On 2 Apr., 11:37, "Bob Phillips" <bob.phill...@somewhere.com> wrote:
> Try
>
> =3DIF(ISNUMBER(MATCH(H7;Sheet1!$O$1:$O$7;0));INDEX(Sheet1!$H$1:$H$7;MATCH=
(H7;=ADSheet1!$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: =3DIF(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

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

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" <thommes@c2i.net> wrote in message 
news:ce578891-4975-48e8-96ea-f61a2efb4c23@u31g2000yqb.googlegroups.com...
On 2 Apr., 11:37, "Bob Phillips" <bob.phill...@somewhere.com> wrote:
> 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
>

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

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

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