vlookup returns na
in [Functions]
|
From: thommes on 2 Apr 2010 04:31 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
From: Bob Phillips on 2 Apr 2010 05:37 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(a)c2i.net> wrote in message news:d13dc0f5-cdbd-4cfc-9784-719370adbfea(a)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
From: thommes on 2 Apr 2010 05:55 On 2 Apr., 11:37, "Bob Phillips" <bob.phill...(a)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
From: Mike H on 2 Apr 2010 06:08 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 > . >
From: Mike H on 2 Apr 2010 06:12
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 > > . > > |