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

1. ## 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.

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

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

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

Or try:

=VLOOKUP(MAX(C40:C47),CHOOSE({1,2},C40:C47,B40:B47),2,FALSE)

4. ## 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. ## 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

6. ## 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.

#### Thread Information

##### Users Browsing this Thread

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

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