+ Reply to Thread
Results 1 to 6 of 6

Vlookup persistent #N/A error?!?!

  1. #1
    Registered User
    Join Date
    08-11-2019
    Location
    New Zealand
    MS-Off Ver
    2010
    Posts
    2

    Vlookup persistent #N/A error?!?!

    Hi all,

    Been scratching my head over this one. Basically I'm trying to create a spreadsheet for my everyday use.

    The spreadsheet consists of doing a series of simple additions. Then what I want it to do is then use vlookup to look up the maximum value from my 8 equations then output the governing equation.

    I've tried formatting my cells to make sure they're all numbers/letters format. Then tried making sure there are no odd spaces etc. Then tried checking character length etc, but can't make it work!

    The bit I'm trying to output is in the 'Governing' cell which I've highlighted in a red box already. (To be more concise, I've determined that my max is 7.44kPa then I want it to output G + Ql + Ws,up? But keeps giving me the #N/A error)

    Any help is appreciated.

    Excel file attached.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,371

    Re: Vlookup persistent #N/A error?!?!

    Hi & welcome to the board.
    How about
    =INDEX(B40:B47,MATCH(MAX(C40:C47),C40:C47,0))

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,439

    Re: Vlookup persistent #N/A error?!?!

    Or try:

    =VLOOKUP(MAX(C40:C47),CHOOSE({1,2},C40:C47,B40:B47),2,FALSE)
    Last edited by Phuocam; 08-11-2019 at 09:48 AM.

  4. #4
    Registered User
    Join Date
    08-11-2019
    Location
    New Zealand
    MS-Off Ver
    2010
    Posts
    2

    Re: Vlookup persistent #N/A error?!?!

    Thanks guys, I'm still a novice user with this kind of stuff, appreciate the help.

    May I ask what I did wrong so that I know for next time? Thanks again!

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,459

    Re: Vlookup persistent #N/A error?!?!

    VLOOKUP() only searches for lookup_value in the first/leftmost column of the lookup range. In your named range SLS (B40:C47), VLOOKUP() is going to search in B40:B47 for the max value, but those cells do not contain the numbers, they contain various text strings. Because the max value will never match any of those text strings, it will always return N/A. Phuocam's solution effectively changes the position inside the function of the two columns. Fluff13's solution uses the MATCH() function to search column C and then uses the INDEX() function to return the appropriate value from column B.

    As a novice user, you may want to spends some time with the help files for these functions (look under lookup and reference functions) https://support.office.com/en-us/art...1-63f26a86c0eb
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,023

    Re: Vlookup persistent #N/A error?!?!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] application vlookup error runtime error 1004, unable to get the vlookup property of the.."
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 12-05-2018, 12:03 PM
  2. How can I keep a formula persistent down a column?
    By gilligan8 in forum Excel General
    Replies: 9
    Last Post: 06-25-2014, 11:00 AM
  3. Formula not being persistent when copied
    By ks100 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-29-2013, 03:58 PM
  4. [SOLVED] Persistent decimal rounding
    By Trevasaurus in forum Excel General
    Replies: 4
    Last Post: 03-20-2013, 04:27 PM
  5. Making vlookup persistent
    By 91TwighlightGT in forum Excel General
    Replies: 15
    Last Post: 11-07-2011, 04:54 PM
  6. Persistent Formula / Graph
    By haysmj in forum Excel General
    Replies: 6
    Last Post: 11-18-2008, 10:38 AM
  7. Persistent Data in a Combo Box
    By The Hawk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-30-2005, 06:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1