[Closed] VLOOKUP.. argh..!

You are trying to find the countries (B) by looking for the ISO (C)? Dont think you can do it that way?

[i]A drawback of the VLOOKUP function is that it can only look up values in the leftmost column of a table.[/i]

Quick guess: is range lookup allowed by default? So it's assuming the list is alphabetical and it's not and so it throwing up an out of sequence result? Chuck a false in there as a fourth argument.

Edit: Actually, what stato said.

change your last argument from 1 to 0

edit: actually add a 4th argument = 0

You're trying to return a result from the first column in the lookup table which doesn't work. The initial lookup in C5 needs to be the same as the matching value in the first column of your reference table 'Data Definition'!B4:C254

The result you're looking to return needs to be in the second
column and the formula would be :

=vlookup(c5,'Data Definition'!B4:C254,2,false)

this will look at what is in C5, find the first matching entry in the lookup table and return the value to the right of the matched entry in the lookup table. i.e in Column 2

You need to reorder the columns. Swap B and C over.

Looks like it only looks up from left to right.

So the data definition was Country Name/ISO code. I copied it and changed it to ISO code/Country Name and it worked.

BUT

When I tried to autopopulate the rest of the rows, it incremented ALL the row references, including the ones in the reference range!

So I wanted this:

=VLOOKUP(C5, G5:H255, 2)
=VLOOKUP(C6, G5:H255, 2)
=VLOOKUP(C7, G5:H255, 2)

and I got this:

=VLOOKUP(C5, G5:H255, 2)
=VLOOKUP(C6, G6:H256, 2)
=VLOOKUP(C7, G7:H257, 2)

Honestly. FFS.

if you are unable to change the structure of the Data Definition table to put the ISO in the left most column, you can use MATCH and OFFSET instead

e.g.

=offset( 'Data Definition'!$b$4, match(C5, 'Data Definition'!$B$4:$C$254)-1, 0, 1, 1)

=VLOOKUP(C5, G$5:H$255, 2)

How much do you earn an hour? payment by paypal please 😀

=VLOOKUP(C5, G:H, 2)

You are using relative references in your lookup table e.g. C5 rather than absolutes e.g. $C$5

add a $ before the row and column refs in your original formula and recopy. f4 is your friend here.

It'll work then.

just remove the numbers so that your 2nd argument reads G:H

In true STW, rather than answering your question I would suggest doing something different......
VLOOKUP isn't a great function imho. Far better and more flexible is to use a combination of INDEX/MATCH or OFFSET/MATCH.

Thanks. Sorted.

Not come across that $ thing before. How on earth can C5 be a relative reference? Confused.

How on earth can C5 be a relative reference? Confused.

It's a hangover from the earliest versions which has been retained as it is a useful feature for many users.

Unless you instruct it otherwise by using the $, If you put =c5 in cell d6 Excel actually reads the formula internally as [b]=row-1,column-1[/b] as that was how the first versions worked. You can alter the cell reference structure to R1C1 format if it makes it easier.

With the original data set:

=INDEX('Data Definition'!B:B,MATCH(C5,'Data Definition'!C:C,0))

I seemingly do this kind of formula a gazillion times a day.... yay me.

Just use MATCH and INDEX. Much more flexible.

[beaten to it...]

Not come across that $ thing before. How on earth can C5 be a relative reference? Confused.

Really ? Without the $ it is always relative and always changes when you copy paste. That's just how Excel has always worked.

The thing that always bugged me with lookup is having to have the values sorted

The thing that always bugged me with lookup is having to have the values sorted

doesnt need to be sorted
Use FALSE for match type to make sure it brings back exact matches though

C5 is always an absolute [i]reference[/i]. I guess the $ must mean it's immutable.

EDIT I think I see what PP is on about. It's an absolute reference derived by relative means. Pfft.

Using Range Names for Table of Data would also avoid the need to use $ signs, which really are primary level Excel. Good practice too.

Using Range Names for Table of Data would also avoid the need to use $ signs, which really are primary level Excel. Good practice too.

Range names are great but its much harder to quickly sense check large sheets with multiple calcs without checking every single range is still correct after someone else who might not be as proficient has been messing with the sheet. At least with bare references you can check consistency or just re-apply as a Quality check.

VLOOKUP is for pussies. INDEX(MATCH) is where the cool kids hang out.

Named ranges are a ballache for model auditing.
I never use them, and I when Im coaching I generally advise to avoid them in favour of better model design.

Dynamic ranges FTW!

Dynamic ranges for graphing is permitted 🙂

*for everything!!

If you really want to baffle anyone use array formulae on dynamic ranges, that'll kill someone's afternoon.

Oh and if you're using lookup or match remember the error handling, nothing worse than all those #NAs

Using Range Names for Table of Data would also avoid the need to use $ signs, which really are primary level Excel.

You lot know Excel isn't my job, don't you?

I do love how devs look down on Excel, but when it comes time to use it they can't get their massive heads around it

I don't look down on it. I use it for its job. Just one of the huge array of tools, skills and technologies in my massive head 🙂

You lot know Excel isn't my job, don't you?

And you don't know about $ fixing? Career change?

(I found out about it literally yesterday, so am of course an expert.)

You lot know Excel isn't my job, don't you?

I had assumed you were unemployed as your occupation appears to be posting on here - Excel is a life skill now, my daughter was taught about $ fixing at primary school hence my comment.

I'm pretty sure I was taught absolute referencing in year 8 doing CLAIT 🙂

just remove the numbers so that your 2nd argument reads G:H

Don't do this. VLookups use quite a lot of memory, and making the references >1million rows does little to help!

Excel is a life skill now, my daughter was taught about $ fixing at primary school hence my comment.

You know what else is a life skill? Google, and posting on the internet to find answers 🙂

On the more important point about range names, I tend to use pretty obvious names so it is self evident where I am picking up from. On the odd occassion when my stuff has been audited, they have found it easy to follow. The other practice that I think is good for vlookups is to use the COLUMNS function to count across. This means if you add a column of data it will automatically adjust and your calcs aren't thrown out.

EDIT: I also have a table of Range Names on the front sheet that is updated when the sheet is opened.

All good tips thanks folks. Will come in handy in the future, but in this case all I wanted was a one-time list of strings to paste into something so long term maintenance isn't an issue.

Re education though - I wasn't taught Excel of course. In school we had a few hours on some BBC word processor that wasn't WYSIWYG, and at 6th form college in Computer Studies we actually did learn about punched cards. Most of my contemporaries are the same. So we look like bumbling oafs with office software as we design enterprise systems.

A friend of my wife's struggling to manage her small business without using any IT whatsoever because she has no idea what to do, doesn't even own a computer. Even though she's younger than us - and is degree educated. When Office skills are as fundamental as arithmetic and reading, we're going to look a bit rubbish if we aren't careful.

When you look at old people fumbling with technology, and you wonder if that will one day be you - well this is how it starts.

molgrips - Member
I don't look down on it. I use it for its job.

I'll bet you do, actually - whereas *way* too many people use it for absolutely [i]everything[/i].

I'll bet you do, actually - whereas *way* too many people use it for absolutely everything

I'll counter that with way too many people can only use about 4 functions on it and then it comes to getting some meaningful data out of it and they're going through it manually with a calculator in one hand.

I went on an intermediate Excel course through work and it was very interesting - partly because I learnt some new stuff but partly because it was eye opening seeing how little most people knew about it. All the examples the teacher used were from real-time scenarios where people were manually trawling through data that Excel could have calculated in seconds.

whereas *way* too many people use it for absolutely everything.

A large part of my job is providing the enterprise replacement for the traditional big pile of spreadsheets, so I'd agree with that 🙂 Generally some hero has hand-crafted these things and no-one knows what's inside them or why they are like they are.

The problem with excel is its designed for people who know what they want to do with it.

As soon as google work out a way to combine their search engine with a spreadsheet Microsoft will die. Imagine just being able to describe what you want to be done, and getting the response with additional info about what it has (and has not) done.

Some really big financial decisions were found to be being made using 'data' from really shonky spreadsheets. It's scary.

There are loads of tasks that I complete quickly using spreadsheets and some algorithmic thinking. Watching colleagues stumble through the same stuff manually is embarrassing.

should we let him in on the F4 secret?

Sorry for being stupider than a primary school kid, my education has been very poor.. but...

What do you all think this does in a cell?

=IF(ISBLANK(F6), "", CONCAT("Check rule ", F6))

I want it to make the value of the cell 'Check rule X' where X is the contents of the cell in F, *unless* that cell is blank, in which case make the cell blank.

However it doesn't work - with the blank cells in F I get 'Check rule '

What am I missing?

Try this. Worked for me.

=IF(ISBLANK(F6), "", CONCATENATE("Check rule ", F6))

Fffffuuuu....

So I click on the cell F6, clear contents, and it works. There are invisible characters in the cells.

AARGH

EDIT and if I use TRIM() it stops working altogether, even if I also clear contents.

Probably a zero in the cell and excel is set to not display zero values.

Not the same as blank.

Nope.

Pilot error then. 😉

try CLEAN instead of TRIM

I'd have approached it from the other end TBH

It's always easier to make a formula conditional on a positive result e.g =if (f6>0..... than a negative result e.g =IFBlank(f6....

I'd have done the formula like this. (assumes a numerical value in f6)

=if(f6>0,CONCATENATE("check Rule ",f6),"")

This way it'll always default to blank unless the positive condition is fulfilled.

Edit, this will work for text values of f6 also but will catch non displaying zeros

[quote="miketually"]Some really big financial decisions were found to be being made using 'data' from really shonky spreadsheets. It's scary.
Are you looking at me ? 😀

I can confirm that "scary" is quite an understatement... I'm looking at things in the £billions here.

Thanks.

Gave up in the end - much as I want to find out what's going on, I have code to write. Cleared out the cells by hand.

[quote="Rob Hilton"]...whereas *way* too many people use it for absolutely everything.

Using Excel as a word processor.
Using Word as a spreadsheet.

Once, when I had asked for detail of some hours from timesheets, the person took screengrabs from Excel and pasted them into Word as images, then sent me that. WTF ???