• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

excel v lookup

Question on a spreadsheet with two cells not using vlookup correctly:

The spreadsheet has one column per client, and shows which stocks are held and this holding as a % of the total portfolio.

The spreadsheet populates using a VLookUp formula, extracting the relevant data from another excel spreadsheet.

One client/column does not populate the relevant data for a particular stock, even though it uses the same formula as the other cells in the column and there are no obvious mistakes. The formula for these two cells are as follows:

=IF(ISNA(VLOOKUP(A138,'Z:\Yahoo NS Valuations\nsValuation\2013\09.2013 NS\[nsValuation 2013.09.13.xlsm]E Brett'!$A$5:$L$196,4,FALSE))=TRUE,0,VLOOKUP(A138,'Z:\Yahoo NS Valuations\nsValuation\2013\09.2013 NS\[nsValuation 2013.09.13.xlsm]E Brett'!$A$5:$L$196,4,FALSE))

=IF(ISNA(VLOOKUP(A139,'Z:\Yahoo NS Valuations\nsValuation\2013\09.2013 NS\[nsValuation 2013.09.13.xlsm]E Brett'!$A$5:$N$196,14,FALSE))=TRUE,0,VLOOKUP(A139,'Z:\Yahoo NS Valuations\nsValuation\2013\09.2013 NS\[nsValuation 2013.09.13.xlsm]E Brett'!$A$5:$N$196,14,FALSE))

We have tried copying over cells where the formula works, checking the cell format, checking the reference cell and moving the reference cell destination.
3 Solutions
Rob HensonFinance AnalystCommented:
The Holding is looking at A138 whereas the Percentage is looking at A139. Is that correct?
Your formula looks fine.
Check the reference data doesn't have leading / trailing spaces and/or single quotation marks.

Alternatively can you post example ?
barry houdiniCommented:
to clarify:

I assume the ISNA part of the formula is kicking in and you get zero returned for both formulas? If so then it menas that A138/A139 isn't found in the lookup range - what's in those cells?

regards, barry
doctorbillAuthor Commented:
those cellesare empty
doctorbillAuthor Commented:
Thanks all - given me some pointers to look at
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now