Vlookup
Posted by Jack on November 09, 2001 2:40 PM
Why does Vlookup fail in large SS and just return incorrect data from sheet 1 over to sheet 2, ive never solved this, say 2000 rows, if i break up into 100 or 200 fine and fast... what we need is a cool Vlookup that is user friendly to copy and paste.
say blamk if no data not NA and return correct results on medium SS
Any ideas, i thoght vlookup = true & vlookup = fal;se & vlookup = ""
Bigthanks in advance, or would it be best to script out custom function
Rdgs :-)
Posted by Aladin Akyurek on November 09, 2001 2:49 PM
Jack,
See Archive 2 on this site regarding a proposal to MS incorporating your concern about #NA.
But, I don't understand why you'd get incorrect results. Care to elaborate a bit on this?
Aladin
=========
Posted by Jack on November 09, 2001 3:01 PM
Aladin, Thanks for your time, seems Excel can play up on big vlookup say simple =vlookup(A1,sheet1!A1:Z1000,13)
Copy and pate and some times it returns incorrect results, i do this most weeks and if i split the 1000 or so into small chunks it works fine??? Odd that, what i want is simple vlookup to do thousands of check from sheet 1 and carry ovre to sheet 2 which is very common after all. sheet size 7 to 8 mb and A to AG used by 1000 to 2000 rows. You know i know what im doing and so on but vlookups seem to be touchy, im looking for a work arround, thought someone must know.
My ideas was to has 3 vlookups on if statments, so one is true - one is false - last is nothing ie "" so NA is not returned, but also in an effort that the formula is user friendly.
eg =if(iserror(vlookup(A1.....false),Vlookup ...TRUE, vlookup....."")))
Any ideas... or do i look at the opperater as the proble ie ME!
Posted by Jack on November 09, 2001 3:01 PM
Aladin, Thanks for your time, seems Excel can play up on big vlookup say simple =vlookup(A1,sheet1!A1:Z1000,13)
Copy and pate and some times it returns incorrect results, i do this most weeks and if i split the 1000 or so into small chunks it works fine??? Odd that, what i want is simple vlookup to do thousands of check from sheet 1 and carry ovre to sheet 2 which is very common after all. sheet size 7 to 8 mb and A to AG used by 1000 to 2000 rows. You know i know what im doing and so on but vlookups seem to be touchy, im looking for a work arround, thought someone must know.
My ideas was to has 3 vlookups on if statments, so one is true - one is false - last is nothing ie "" so NA is not returned, but also in an effort that the formula is user friendly.
eg =if(iserror(vlookup(A1.....false),Vlookup ...TRUE, vlookup....."")))
Any ideas... or do i look at the opperater as the proble ie ME!
Posted by Aladin Akyurek on November 09, 2001 3:38 PM
Jack --
> My ideas was to has 3 vlookups on if statments, so one is true - one is false - last is nothing ie "" so NA is not returned, but also in an effort that the formula is user friendly.
> eg =if(iserror(vlookup(A1.....false),Vlookup ...TRUE, vlookup....."")))
This puzzles me. It looks as an impossible construct to me. Just for the sake of argument, if this would be possible, you'd have a tremendous efficiency problem. Three times vlookup for a simple fetch would be just too much.
BTW, did you try using a formula similar to:
=IF(COUNTIF(SheetX!A1:A2000,A1),VLOOKUP(A1,SheetX!A1:F2000,5),"")
This formula tries to establish whether the lookup value A1 is available in column 1 of the table SheetX!A1:F2000. If true, it will fetch the value associated with A and will return "" otherwise.
Aladin
======= , Thanks for your time, seems Excel can play up on big vlookup say simple =vlookup(A1,sheet1!A1:Z1000,13)
Posted by Aladin Akyurek on November 09, 2001 3:38 PM
Jack --
> My ideas was to has 3 vlookups on if statments, so one is true - one is false - last is nothing ie "" so NA is not returned, but also in an effort that the formula is user friendly.
> eg =if(iserror(vlookup(A1.....false),Vlookup ...TRUE, vlookup....."")))
This puzzles me. It looks as an impossible construct to me. Just for the sake of argument, if this would be possible, you'd have a tremendous efficiency problem. Three times vlookup for a simple fetch would be just too much.
BTW, did you try using a formula similar to:
=IF(COUNTIF(SheetX!A1:A2000,A1),VLOOKUP(A1,SheetX!A1:F2000,5),"")
This formula tries to establish whether the lookup value A1 is available in column 1 of the table SheetX!A1:F2000. If true, it will fetch the value associated with A and will return "" otherwise.
Aladin
======= , Thanks for your time, seems Excel can play up on big vlookup say simple =vlookup(A1,sheet1!A1:Z1000,13)
Posted by Jack on November 09, 2001 3:45 PM
Aladin, thanks for your formula, ill give it a play at home and take to work Monday maybe its me, but ill give best shot and promise ill post in the week the results when your posting, im sure you right.
Many thanks
PS What does BTW mean?
Posted by Jack on November 09, 2001 3:45 PM
Aladin, thanks for your formula, ill give it a play at home and take to work Monday maybe its me, but ill give best shot and promise ill post in the week the results when your posting, im sure you right.
Many thanks
PS What does BTW mean?
Posted by Aladin Akyurek on November 09, 2001 4:06 PM
BTW = By the way. (NT)
Posted by Aladin Akyurek on November 09, 2001 4:06 PM
BTW = By the way. (NT)
