vlookup w/array in another sheet??

badinvestor

New Member
Joined
Dec 28, 2009
Messages
21
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!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Nooblet

Board Regular
Joined
Feb 18, 2010
Messages
56
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

New Member
Joined
Dec 28, 2009
Messages
21
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

Board Regular
Joined
Feb 18, 2010
Messages
56
Does your lookup_value always exist in the first column of the table_array?

Also, what version of excel are you using?
 

AdamL

Well-known Member
Joined
Sep 25, 2008
Messages
767
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

New Member
Joined
Dec 28, 2009
Messages
21
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top