vlookup w/array in another sheet??

[badinvestor]

i've looked all over the other threads and can't quite find what i need. i need to find a value where the lookup_value is on the the same sheet that contains the formula but the array etc. are on sheet 2.
e.g. sheet 1 has two cols. col1 contains a ref # and col2 needs to return the value from sheet 2 where the ref # matches col1 sheet 1. any help would be great!

 

[Nooblet]

This syntax would look for a lookup_value in a table_array on Sheet 2.

=VLOOKUP(lookup_value,Sheet2!table_array,col_index_num,[range_lookup])

eg. =VLOOKUP(A2,Sheet2!A1:C100,2,0)

(This would go in column 2 of sheet 1 and would return the value from column 2 of the table array in sheet 2 that corresponds to the ref# in column 1 of sheet 1)

Is that the kind of thing you were after?

 

[badinvestor]

Noobelt this is formula that I'm using. but all I get is N/A
=IF(VLOOKUP(A1,Sheet2!$B1:$D1000,3,0)<0, -VLOOKUP(A1,Sheet2!$B1:$D1000,3,0)," ")
sorry if i made it seem easier at first. this formula is in Sheet1!C1 returning the #N/A. any idea?

 

[Nooblet]

Does your lookup_value always exist in the first column of the table_array?

Also, what version of excel are you using?

 

[AdamL]

this formula is in Sheet1!C1 returning the #N/A. any idea?

The formula will return #N/A if the VLOOKUP can't find the value. So if you expect to find a match in Sheet2!, it may be that the data in that sheet isn't clean (may be trailing or leading spaces, etc).

If you want the formula to return a blank rather than #N/A if no match is found, then try:

=IF(ISNUMBER(MATCH(A1,Sheet2!$B1:$B1000,0)),IF(VLOOKUP(A1,Sheet2!$B1:$D1000,3,0)<0,-VLOOKUP(A1,Sheet2!$B1:$D1000,3,0),""),"")

or perhaps:

=IF(ISERROR(SQRT(-VLOOKUP(A1,Sheet2!$B1:$D1000,3,0))^2),"",-VLOOKUP(A1,Sheet2!$B1:$D1000,3,0))

or in Excel 2007 or later:

=IFERROR(SQRT(-VLOOKUP(A1,Sheet2!$B1:$D1000,3,0))^2,"")

 

[badinvestor]

The formula will return #N/A if the VLOOKUP can't find the value. So if you expect to find a match in Sheet2!, it may be that the data in that sheet isn't clean (may be trailing or leading spaces, etc).

If you want the formula to return a blank rather than #N/A if no match is found, then try:

=IF(ISNUMBER(MATCH(A1,Sheet2!$B1:$B1000,0)),IF(VLOOKUP(A1,Sheet2!$B1:$D1000,3,0)<0,-VLOOKUP(A1,Sheet2!$B1:$D1000,3,0),""),"")

or perhaps:

=IF(ISERROR(SQRT(-VLOOKUP(A1,Sheet2!$B1:$D1000,3,0))^2),"",-VLOOKUP(A1,Sheet2!$B1:$D1000,3,0))

or in Excel 2007 or later:

=IFERROR(SQRT(-VLOOKUP(A1,Sheet2!$B1:$D1000,3,0))^2,"")

AdamL the ISNUMBER did it. Thanks for the help.