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

# 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:

Holding:
=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))

Percentage:
=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.
0
doctorbill
3 Solutions

Finance AnalystCommented:
The Holding is looking at A138 whereas the Percentage is looking at A139. Is that correct?
0

Commented:
Your formula looks fine.
Check the reference data doesn't have leading / trailing spaces and/or single quotation marks.

Alternatively can you post example ?
0

Commented:
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
0

Author Commented:
those cellesare empty
0

Author Commented:
Thanks all - given me some pointers to look at
0
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.

## Featured Post

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