I am trying to write a VLOOKUP table and keep getting the error message "#N/A"- "A value is not available to the formula or function." I am using "=if(B11=Tally!$G$35,VLOOKUP(BA11,Tally!$F34:$L$56, 5, 0),0)."

B11 is the input cell and "Tally!$G$35" is the location of the name used in B11; "BA11" contains the value that I am looking for in the table; "Tally!$F34" is the upper left cell in the table and ""Tally!$L56" is the bottom right cell. The value that I am looking for is in column 5 of the table.

Thank you.

Brian W

✔ Best Answer

when I run the formula below, I get "False."Then B11 does not equal Tally!G35

To begin, I would check the cell contents of both B11 & Tally!G35.

MIKE

Your post is confusing. First you said:

""BA11" contains the value that I am looking for in the table"Then you said:

"The value that I am looking for is in column 5 of the table."VLOOKUP looks for the lookup_value in Column

1of the table_array, not Column 5.See my confusion?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dear DerbyDad03, Thank you for your response. I am trying to get the lookup_value for items in column 5 of the table_array.

BrianW

I still don't get what you are asking for.

I am trying to get the lookup_value for items in column 5 of the table_array."...the lookup_value for items in column 5..." doesn't make any sense.

The lookup_value is what VLOOKUP looks for the first column of the table_array - Tally!F34:F56 in your case.

If it finds the lookup_value (the contents of BA11) in Tally!F34:F56, it will return the contents Tally!Column J of the same row.

If that's what you want, and the VLOOKUP is returning #N/A, then it's not finding whatever is in BA11 in Tally!F34:F56. It's really that simple.

If that's not what you want, then you'll either have to change the VLOOKUP formula or use something else.

As written, your formula works fine, unless it's not really what you want to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dear DerbyDad03, Thank you for your reply; unfortunately, I can not get the table to work correctly. The formula was successfully used in another worksheet using Excel 2003; I am now using Excel 2010 with the spreadsheet in "Compatability Mode." Is there a difference in how these two versions of Excel process a VLOOKUP table?

Thank you.

Brian W

There should be no difference between the 2 as far as VLOOKUP is concerned, but of course I can't tell from here. I set up a workbook based on your description and it seems to work fine based on how VLOOKUP is supposed to work.

However, I'll still don't know what you mean by:

"I am trying to get the lookup_value for items in column 5 of the table_array. "Perhaps if you posted an example of your data it would make more sense.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dear DerbyDad03, Unfortunately, I can not post an example of my data due to its confidential nature. Could you post your sample so I could compare them?

Thank you.

Brian W

Dear DerbyDad03, I rebuilt the VLOOKUP table and it worked when it was on the same spreadsheet as all the needed inputs. However, when I moved the table to a different spreadsheet and referenced the locations in the formulas as New!start cell:end cell, it did not work.

What are I doing wrong?Thank you.

Brian W

New!start cell:end cellIf it's a New Book, then you need to give it the New Book name also.

=LOOKUP(A1,[NewBook.xlsm]Sheet1!$A$1:$B$27,5)

MIKE

re: "Unfortunately, I can not post an example of my data due to its confidential nature. "It's not about the data itself, it's about your formulas, referenced cells, etc.

You could use cats, dogs and colors as your data. The formulas will either work based on the layout or they won't.

This is what your formula tells me that you are trying to do, because this is the only way the formula will work:

- You have 2 sheets.

- One is named Tally

- The other is named..I don't know...let's call it Sheet1

- Somewhere on Sheet1 you have this formula:=IF(B11=Tally!$G$35,VLOOKUP(BA11,Tally!$F34:$L$56, 5, 0),0)

- In Tally!$G$35 you have some data. I'll use CowPie.

- In Sheet1!BA11, you have some data. I'll use Mucus.

- In Sheet1!B11, you have some data.

- If the data in Sheet1!B11 happens to be CowPie, you want VLOOKUP to find Mucus in Tally!$F$34:$F$56 and return the data in Tally!Column J, in the same row in which Mucus was found.

- If the data in Sheet1!B11 is not CowPie, you want the formula to return 0.I set up a workbook as described above and here's what happens:

- If the data in Sheet1!B11 is

notCowPie, the formula returns 0, as expected.

- If the value in Sheet1!B11 is CowPie and Mucus is found in Tally!$F$34:$F$56, the formula returns the value in TALLY!Column J, in the same row in which Mucus was found, as expected.

- If the value in Sheet1!B11 is CowPie and but Mucus isnotfound in Tally!$F$34:$F$56, the formula returns #N/A, as expected.It works exactly as I would expect it to work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Brian,

Are you sure both sets of data your using are the same?

Check and make sure there is no hidden space character

floating around to muck things up.MIKE

DerbyDad03 and mmcconaghy, Thank you for your assistance but I still have not gotten the formula to work correctly on its intended spreadsheet. I checked the locations for all of the items referenced in the formula and they are correct; however, when I run the formula below, I get "False."

=if(B11=Tally!G35,Vlookup(BA11,Tally!f35:ad56,5,0),false)B11 is an input cell for a name which is typed into this cell; the name is at Tally!G35

BA11 is a input cell for a name which is selected from a drop down menu. This name is also in the leftmost column of the table array.

Tally!f35:ad56 is the range of the table array.

5 is the column in the array that has the correct value.This would indicate to me that the formula is not referencing the given locations but I don't know how to fix that.

Thank you,

Brian W

when I run the formula below, I get "False."Then B11 does not equal Tally!G35

To begin, I would check the cell contents of both B11 & Tally!G35.

MIKE

In your first post, you said that the VLOOKUP was returning #N/A. That tells me that originally the value in B11 must have equaled Tally!G35, otherwise the VLOOKUP portion would never have been evaluated.

Now you say that your formula is returning FALSE, which means that B11 must

notequal Tally!G35.What changed?

To follow up on Mike's response, enter this formula on the same sheet as the B11 you mentioned above.

=B11=Tally!G35

If this does not return TRUE then B11 does not equal Tally!G35. Until it does, there no point in working on the VLOOKUP issue.

One more thing: Each formula that you have posted contains lower case letters where there should be upper case letters. That tells me that you are not copying your formulas from your spreadsheet into your posts. That makes me concerned that perhaps we are not seeing something that we should be.

In the future, please copy the formulas directly from the formula bar and past them into your post.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad03 and mmcconaghy, I believe I have found the error but do not know how to correct it.

When I enter =BA11, I get VC, the correct response.

When I enter = Tally!F35 (the location of VC in the vlookup table array, I get VC, the correct response.

When I enter =BA11=Tally!F35, I get FALSE.I have checked the format, the length of the cell, the alignment, but these have not corrected the problem. How do I check for hidden character which Mike had mentionned? What else could be the problem?

You guys have been a great help. I would like to contribute $25 towards the upkeep/maintenance of this site; how would I do that?

Thanks,

Brian W

Is this (VC) the only entry you are having a problem with or does the problem occur with every value in Tally!F34:F56? Just curious...how do the values get into BA11 anyway?

Either way, it seems to me that if you simply retyped the values in BA11 and Tally!F35, they would then match. A Copy/Paste would be even better.

Even if you had to retype all of the values in F34:F56, you'd solve the problem is less time than trying find out why they don't match now.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dear DerbyDad03, Thank you for your assistance. I finally solved the problem. Uisng "LEN", I discovered that cells I thought were the same were, in fact, different because of hidden characters which I was able to remove.

You guys have been a great help. I would like to contribute $25 towards the upkeep/maintenance of this site; how would I do that?

Brian W

Even though all of the members of this site are volunteers, the site itself is funded through advertising revenue. Private donations are not required and can't be accepted.

Pick a charity and send them the $25. In other words, pass it forward.

As Douglas Spaulding ("Dandelion Wine", by Ray Bradbury) reflected after having his life saved by Mr. Jonas:

"How do I thank Mr. Jonas, he wondered, for what he's done? How do I thank him, how to pay him back? No way, no way at all. You just can't pay. What then? What? Pass it on somehow, he thought, pass it on to someone else. Keep the chain moving. Look around, find someone, and pass it on. That was the only way...."

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History