vlook up not working

[paulr0700282]

Hi

I have a formula as follows =VLOOKUP(A7,Tables!X3:AB22,3)

A7 = West ham united

but the formula is producing the result for Crystal palace !!!!

ive tried using TRUE & FALSE within the formula and no joy


I need to either understand why v look up is returning a value associated with a different cell or an alternative way of searching and returning the correct result , please

[shivya]

Please attach your excel sheet so that i can check what is the issue with?

[MrShorty]

Difficult to impossible to debug with only the information given. The results you are getting are probably dependent on what your lookup table looks like.

Is your lookup table sorted by the leftmost column in ascending order or not? If not, then VLOOKUP(value,table,column,TRUE) cannot work correctly, as the binary search option only works on sorted lists.

Is the exact text string "West ham united" present in the leftmost column of the lookup table? The exact match (4th argument=FALSE) looks for exact matches, which means exact character for character match (except not case sensitive). Leading/trailing spaces or other non-printing characters can make something that looks like match not be seen as a match.

Debugging this requires looking at your lookup table. Since you provided no information about your lookup table, we cannot debug this for you. However, those two items should give you a starting place to debug.

[AliGW]

OK - well, there are lots of possibilities! Best thing is to attach a sample file that shows the error, but you could try this:

=VLOOKUP(A7,Tables!X3:AB22,3,0)

You need to make sure that A7 really does match the entry for West Ham Utd in column X of the lookup table EXACTLY.

[Zer0Cool]

paulr0700282,

As is, your formula is looking for an approximate match. VLOOKUP has 4 arguments, the 4th being optional. When omitted the default is TRUE or an approximate match. Generally this produces unexpected results when doing a lookup on text. You want to explicitly set this argument to FALSE (or 0, they are the same thing to Excel).

Having said that, be aware that exact matches leave no margin of error or wiggle room if you will. "Bob" and " Bob " are not the same thing to Excel. 1 as text and 1 as a numeric value are also not the same. Typically you want to do what is often called "normalizing your data" when doing a lookup like this. That means getting your lookup values and the column of values you are looking in within the data array/range to be in like terms. Remove excess spaces, non-print characters, same data types, and sometimes even modifying data to be in a similar syntax (ex: first name last name for both instead of one having a middle initial and the other not).

Without a sample to inspect its impossible to say exactly why its not working. The above are the most common reasons I see when VLOOKUP returns unexpected results. Also make sure automatic calculation is on if feasible or at least recalculate after modifying the formulas to get accurate results if set to manual calc.

Hope this helps

[63falcondude]

Without seeing the data that you are using this formula on, all that we can tell you is what the formula is doing.

Your formula looks in column X (rows 3 through 22) of the 'Tables' worksheet for the value that is in A7 of the worksheet that the formula is in.

If it finds a match in column X, it then returns the value in the corresponding row of column Z.

Note that VLOOKUP stops at the first match.

If you want an exact match, you will want to specify a range lookup of FALSE.

If you require further assistance, please upload a small sample of your workbook along with the desired result of the formula.

[paulr0700282]

how do I attach?

[jeffreybrown]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[paulr0700282]

hopefully this attachment worked

[paulr0700282]

my aim here is as follows

look for the same value in C7 (Predictions) within the range of X3:AB22 (Tables) and return the value 2 to the right (games played)

[AliGW]

Fixtures - L5 - get rid of the trailing space, then amend the formula to this:

=VLOOKUP(A7,Tables!X$3:AB$22,3,0)

[AlKey]

There are trailing spaces in your Col A
Try this

Please Login or Register  to view this content.

[paulr0700282]

Thank you

as the value in C7 is (='fixtures'C5) The trim option worked a treat

never ceases to amaze me how knowledgeable people on here are - thanks again

[MattDay2u]

Tab "Tables" has loads of hidden columns. May be your issue. I always insert a COUNTING row as the top row above my VLOOKUP targget, so it tells me the return column count without me having to count. Works even if columns are hidden.
You should get something like:
(COLUMN) L M R
(C. COUNT) 10 11 16