Vlookup #REF

[jdotbonk]

Hello All, my formula is working in relation to finding and pulling the correct values/cell I was expecting, but I am getting the below #REF value instead of the actual value I am looking up on the other sheet and expect to see. Is there a formatting issue or could it be something else? Thanks!

 

[Fluff]

The 2nd argument is a single column, but you are telling it to return the 3rd column.

 

[dreid1011]

Disregard.

 

[etaf]

you are using reference columns as B to B
and then say use column 3 - but you are not giving column 3

soi that would need B:D to be reference
as B is the 1st column as first in the range and 3 - is D counting from B

$B$377:$D$20375

which will look down column B and return the matching result from D

 

[jdotbonk]

you are using reference columns as B to B
and then say use column 3 - but you are not giving column 3

soi that would need B:D to be reference
as B is the 1st column as first in the range and 3 - is D counting from B

$B$377:$D$20375

which will look down column B and return the matching result from D

Thank you. I also have two sheets (Borrow & Loans) and am trying to run a vlookup on the loan sheet to search the whole borrow sheet and bring over any symbols that are the exact same and present on both. Is there a way to do this without getting an error?

loan sheet:

borrow sheet:

 

[etaf]

images are too small for me to see

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[jdotbonk]

images are too small for me to see

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Sorry, including example below. Thanks!

#1 loan sheet: trying to use vlookup in column E to search the security link in column B on the borrow sheet

#	Security Link	Symbol	Contract ID	Cross vlookup
982	987654321	AAPL:US	380295778	#N/A
458	987654321	DIS:US	380295851	#N/A
637	987654321	MSFT:US	380295685	#N/A
534	123456789	DRL:US	380295783	#N/A
123	123456789	SPY:US	580295783	#N/A
456	123456789	CPC:US	680295783	#N/A
789	123456789	SOXX:US	480295783	#N/A

#2 borrow sheet: if the security link on the loan sheet is present in the borrow sheet table searched, i need the security link to populate back on the loan sheet lookup column so i know which ones are on both.

#	Security Link	Symbol	Contract ID
982	987654321	AAPL:US	380295778
657	123784596	TBT:US	380295851
238	123784596	MSFT:US	380295685
534	123456789	DRL:US	380295783
123	123456789	SPY:US	580295783
456	123456789	CPC:US	680295783
789	123456789	SOXX:US	480295783

 

[etaf]

you could just use a countif()
bit if you want to pull across the same number
vlookup() or index/match or xlookup

=VLOOKUP(B2,borrow!$B$2:$B$100,1,FALSE)

BUT that is just looking for the key and will not matter where it finds it in the column
if not found then a n/a will be returned
BUT does it need to also match another field at all

LOAN

Book15
	A	B	C	D	E
1	#	Security Link	Symbol	Contract ID	Cross vlookup
2	982	987654321	AAPL:US	380295778	987654321
3	458	987654321	DIS:US	380295851	987654321
4	637	987654321	MSFT:US	380295685	987654321
5	534	123456789	DRL:US	380295783	123456789
6	123	123456789	SPY:US	580295783	123456789
7	456	123456789	CPC:US	680295783	123456789
8	789	123456789	SOXX:US	480295783	123456789
loan

Cell Formulas
Range	Formula
E2:E8	E2	=VLOOKUP(B2,borrow!$B$2:$B$100,1,FALSE)

BORROW

Book15
	A	B	C	D
1	#	Security Link	Symbol	Contract ID
2	982	9.88E+08	AAPL:US	3.8E+08
3	657	1.24E+08	TBT:US	3.8E+08
4	238	1.24E+08	MSFT:US	3.8E+08
5	534	1.23E+08	DRL:US	3.8E+08
6	123	1.23E+08	SPY:US	5.8E+08
7	456	1.23E+08	CPC:US	6.8E+08
8	789	1.23E+08	SOXX:US	4.8E+08
borrow

on a share dropbox- ONLY will be on for a few days

vlookup-etaf.xlsx

Shared with Dropbox

www.dropbox.com