INA help

[Ian]

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

 

[Sheeloo]

Use
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",VLOOKUP(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.

 

[T. Valko]

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)),"",VLOOKUP(A1,Sheet2!$A$1:$B$5000,2,0))

 

[Pecoflyer]

Do you mean that instead of: Sheet2!$A$1:$B$500 you want to expand tha
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)),"",VLOOKUP(A1,Sheet2!$A$1:$B$5000,2,0))

You can probably gain performance by using
=IF(countif(sheet2!$A$1:$b$5000,a1),VLOOKUP(A1,Sheet2!$A$1:$B$5000,2,0),""

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste