Excel vlookup failing for no obvious reason!

NeilF3485

I am using a vlookup function (as I do hundreds of times a day) across 12 columns of data, but for some reason it is failing in one instance only.

I am intentionally not putting in a range_lookup value as I want it to return approximate match if it cannot find an exact match.

Function reads: "=vlookup(N8,$A:$M,N$3+1)
Row 8 = "if(iserror(N7/N6,"-",rounddown(N7/N6,2))
Row 3 = Month number (1 to 12)

This works in 11 of the 12 instances. But fails in month 12 (Col. N)

Now it gets a little more strange - if I amend the function to read: "=vlookup(N8,$A:$M,N$3+1,) - i.e. add in a final comma, then the function works in column N, but fails in all other months instead.

I have tried obvious things, over-typed numbers with hardcoded values, changed the N3+1 to read as "13" instead etc. etc. and nothing works other than adding in the final comma (or ",FALSE")

The lookup table runs: 0%, 60% then upwards in 5% increments. It appears to fail on ANY number which is below 65% - suggesting it isn't recognising the 60% as the nearest match.

I could change the function to read:

"=if(isna(vlookup(N8,$A:$M,N3+1,FALSE)),vlookup(N8,$A:$M,N3+1,TRUE),vlookup(N8,$A:$M,N3+1,FALSE))

To get around it, but it'll still bug the !!!!!! out of me!

Anyone got any ideas on what I'm missing here?!

fluffy70

a social life?

Dave_C_2

I thought the final comma was needed. If there is no argument after the comma Vlookup assumes that it's false.

HTH, Dave

NeilF3485

fluffy70 wrote: »

a social life?

Thanks for this helpful input. I look forward to my social life continuing once I finish my day at work.

Dave_C wrote: »

I thought the final comma was needed. If there is no argument after the comma Vlookup assumes that it's false.

HTH, Dave

Thanks, but I think it's the opposite way around, and that by placing the final comma it assume false, but if it is omitted then Excel treats as both true and false (i.e. searches for an exact but if it can't find it it would do nearest)?

victor2

NeilF3485 wrote: »

Thanks, but I think it's the opposite way around, and that by placing the final comma it assume false, but if it is omitted then Excel treats as both true and false (i.e. searches for an exact but if it can't find it it would do nearest)?

It can't do both - an exact match is also the closest. Putting a comma there seems have the same effect as nothing, ie. TRUE.

According to Excel 2010:
Range_lookup: is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE

Without being able to see some data and the formula with its problem, it is difficult. What version of Excel are you using and can you provide a more detailed example?

NeilF3485

Thanks Victor, that's basically what I meant but didn't word it well. With the comma excel appeared to treat this as a "false" statement (as it worked and returned the exact match), without the comma it was treating it as true (closest match - which could also be exact) - this created the error.

Have found the problem now anyway. The person who created the file containing the lookup table had done one of things which annoys me most in excel and entered a number in white font in a cell in column A which meant that excel was treating the dataset as not being in numerical order.

Changed that cell format to text and it now works, likewise changing the lookup_table from A:M to A3:M45 works.

Thanks for the help.

victor2

It always amuses me when you see some of the "tricks" other people do in spreadsheets. Entering numbers as text, justifying them and adding a space to get the positioning right etc...

You feel so much better when you've sorted it!

Dave_C_2

victor2 wrote: »

According to Excel 2010:
Range_lookup: is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE

Oops You are correct. My brain knew omitted is the same as True, but my fingers typed False. Next time I'll cut and paste:)

Dave (apologetic)

GunJack

victor2 wrote: »

It always amuses me when you see some of the "tricks" other people do in spreadsheets. Entering numbers as text, justifying them and adding a space to get the positioning right etc...

I dunno about amuses, it annoys the carp out of me....especially when you finally find out why your perfectly logical & correct method of trying to do something keeps failing because some twonk has used an obscure custom format that things like vlookup don't match to :mad: