#NA and Vlookup

Posted on 2011-05-09
Last Modified: 2012-06-27
Experts:  I am not sure why I get #NA for the attached.  

I have spent too much time on it.  I dont know why.  If someone could please let me know... thank you EE-LC.xlsx
Question by:pdvsa
    LVL 33

    Accepted Solution

    This is simple! Just need to adjust the row number on your formula. You are looking from row 21 downwards, you should start on row 7, like this:

    =IF(VLOOKUP($B10,'LC Database-JPM (Delete XCR)'!$A$7:$G$145,7,FALSE)="12011Y",-VLOOKUP($B10,'LC Database-JPM (Delete XCR)'!$A$7:$AM$145,23,FALSE),IF(VLOOKUP($B10,'LC Database-JPM (Delete XCR)'!$A$7:$C$145,3,FALSE)="12011",VLOOKUP($B10,'LC Database-JPM (Delete XCR)'!$A$7:$AM$145,23,FALSE),0))

    LVL 33

    Expert Comment

    Please take a look at the attached file.

    LVL 33

    Expert Comment

    For example, your formula on cell H9 was looking for the value of TPTS-652436 that was on 24 of the other sheet. As the formula was starting to search from from 21 downwards, it would find your value. The formula on cell H10 was looking for the value of TPTS-675564 that was on row 13 of the other sheet, thus the formula wouldn't find it.

    Author Comment

    dang it.  I have been looking at too many numbers today.  thank you for the help.  

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now