Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INA help
HI some one was kind enough to show me how this function works, being a
beginner I was wondering if someone could help me figure this out... Ok this is the function I would like to use: =IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,Sheet2!$A$1:$B$500,2,FALSE)) I just need the parameters to search more then 500 cells i would need more in the 5000+ cell range Thanks Ian |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INA help
Use
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",VLOOKU P(A1,Sheet2!A:B,2,FALSE)) It will look for A1 anywhere in Col A [Sheet2] and (if found) return the value from Col B [Sheet2] If no match is found then ISNA will be true and "" will be returned as the formula result. "Ian" wrote: HI some one was kind enough to show me how this function works, being a beginner I was wondering if someone could help me figure this out... Ok this is the function I would like to use: =IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,Sheet2!$A$1:$B$500,2,FALSE)) I just need the parameters to search more then 500 cells i would need more in the 5000+ cell range Thanks Ian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INA help
Do you mean that instead of: Sheet2!$A$1:$B$500 you want to expand that to
Sheet2!$A$1:$B$5000? If so, just make that change in the formula. Tip: FALSE and 0 mean the same thing in 4th argument of this formula. You can save a few keystrokes by replacing FALSE with 0: =IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$5000,2,0)),"",V LOOKUP(A1,Sheet2!$A$1:$B$5000,2,0)) -- Biff Microsoft Excel MVP "Ian" wrote in message ... HI some one was kind enough to show me how this function works, being a beginner I was wondering if someone could help me figure this out... Ok this is the function I would like to use: =IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,Sheet2!$A$1:$B$500,2,FALSE)) I just need the parameters to search more then 500 cells i would need more in the 5000+ cell range Thanks Ian |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INA help
T. Valko;235420 Wrote: Do you mean that instead of: Sheet2!$A$1:$B$500 you want to expand that to Sheet2!$A$1:$B$5000? If so, just make that change in the formula. Tip: FALSE and 0 mean the same thing in 4th argument of this formula. You can save a few keystrokes by replacing FALSE with 0: =IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$5000,2,0)),"",V LOOKUP(A1,Sheet2!$A$1:$B$5000,2,0)) -- Biff Microsoft Excel MVP "Ian" wrote in message ... HI some one was kind enough to show me how this function works, being a beginner I was wondering if someone could help me figure this out... Ok this is the function I would like to use: =IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,Sheet2!$A$1:$B$500,2,FALSE)) I just need the parameters to search more then 500 cells i would need more in the 5000+ cell range Thanks Ian You can probably gain performance by using =IF(countif(sheet2!$A$1:$b$5000,a1),VLOOKUP(A1,She et2!$A$1:$B$5000,2,0),"") -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=65677 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|