+ Reply to Thread
Results 1 to 6 of 6

Vlookup persistent #N/A error?!?!

  1. 08-11-2019, 09:11 AM #1
    james.zfh
    james.zfh is offline
    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
    Reply With QuoteRegister To Reply
  2. 08-11-2019, 09:32 AM #2
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

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

    Hi & welcome to the board.
    How about
    =INDEX(B40:B47,MATCH(MAX(C40:C47),C40:C47,0))
    Reply With QuoteRegister To Reply
  3. 08-11-2019, 09:38 AM #3
    Phuocam
    Phuocam is online now
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,899

    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.
    Reply With QuoteRegister To Reply
  4. 08-11-2019, 07:05 PM #4
    james.zfh
    james.zfh is offline
    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!
    Reply With QuoteRegister To Reply
  5. 08-11-2019, 08:31 PM #5
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,811

    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.
    Reply With QuoteRegister To Reply
  6. 08-12-2019, 01:59 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,434

    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" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. [SOLVED] 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1