VLookup Error

**baffour** · 09-07-2008, 10:56 AM

Hi All
I have created a Vlookup formula but i am getting some errors in the string.
How can i get the formula to display all the values and data in the string? The error occurs when i select London Regional,Newport, or Peterborough in the relevant cell. See formula below:

=IF(A35="Belfast","2064203",IF(A35="Durham","2074203",IF(A35="Glasgow","2054203",IF(A35="GRO","Nil",IF(A35="ION","5004209",IF(A35="Liverpool","2024203",IF(A35="London HQ","5004203",IF(A35="London Regional","5004211",)*IF(A35="Newport","2044203",)*IF(A35="Peterborough","2034203"))))))))

Where am I going wrong with the formula? Hope someone can help

Andy

**VBA Noob** · 09-07-2008, 11:52 AM

Two many if's.

Your better off setting up a table and doing a vlookup

If you must use if's you could try

=IF(A35="Belfast","2064203","")&IF(A35="Durham","2074203","")&IF(A35="Glasgow","2054203","")&IF(A35="GRO","Nil","")&IF(A35="ION","5004209","")&IF(A35="Liverpool","2024203","")&IF(A35="London HQ","5004203","")&IF(A35="London Regional","5004211","")&IF(A35="Newport","2044203","")&IF(A35="Peterborough","2034203","")

VBA Noob

**martindwilson** · 09-07-2008, 11:53 AM

=IF(A35="Belfast","2064203",IF(A35="Durham","2074203",IF(A35="Glasgow","2054203",IF(A35="GRO","Nil",IF(A35="ION","5004209",IF(A35="Liverpool","2024203",IF(A35="London HQ","5004203",IF(A35="London Regional","500421"))))))))

thats it seven nested ifs
to carry on

=IF(A35="Belfast","2064203",IF(A35="Durham","2074203",IF(A35="Glasgow","2054203",IF(A35="GRO","Nil",IF(A35="ION","5004209",IF(A35="Liverpool","2024203",IF(A35="London HQ","5004203",IF(A35="London Regional","500421,aa36)))))))) 
in aa36 put

IF(A35="Newport","2044203",IF(A35="Peterborough","2034203",0))

where aa36 can be any cell you want
to use v lookup which this isnt put them in two adjacent colums on
"places" in one values in the other

**baffour** · 09-07-2008, 01:53 PM

Thanks for the reply folks. As suggested how do i set up a Vlookup formula using a table? The returned data and cell references will remain the same.

**VBA Noob** · 09-07-2008, 01:58 PM

See Cheeky Charlie example

or using your data

VBA Noob

**shg** · 09-07-2008, 02:20 PM

I recommend CC's VLOOKUP. It's easy to maintain, and the table can be put in an out-of-the-way place -- chances are, with some other lookup tables.

There are usually several ways to do something in Excel.

=LOOKUP(A35, {"Belfast","Durham","Glasgow","GRO","ION","Liverpool","London HQ","London Regional","Newport","Peterborough"},
{"2064203","2074203","2054203","Nil","5004209","2024203","5004203","5004211","2044203","2034203"})

A35 is matched in the first list, and the value in the corresponding position in the second list is returned. The values in the first list must be in ascending order.

Here's a way to embed the lookup table in the formula. It's exactly equivalent to CC's suggestion, but harder to maintain. The False argument requires an exact match, allowing the items to be in any order:

=VLOOKUP(A35, {"Belfast",2064203;"Durham",2074203;"Glasgow",2054203;"GRO","Nil";"ION",5004209;
"Liverpool",2024203;"London HQ",5004203;"London Regional",5004211;"Newport",2044203;"Peterborough",2034203}, 2, FALSE)

The commas in the list are column breaks, and the semicolons are row breaks.

**baffour** · 09-07-2008, 03:05 PM

Thanks folks that was really useful. I have picked up a few new things yippie that has just made my day.

**Cheeky Charlie** · 09-07-2008, 12:56 PM

Hold on now, these "if" solutions are perfectly OK, but I really think the point "this is a bad way of doing this" is a little more important(?)

You say "I've made a lookup", but you haven't you've made an enormous nested if formula...

Posting a tiny example of a vlookup to help illustrate what a lookup really is! (The index match eqvuialent is not really relevant, this is something of a generic example)

HTH

**Cheeky Charlie** · 09-07-2008, 02:00 PM

Hi Baffour,

how do i set up a Vlookup formula using a table

To be fair, any answer to this question would basically be a copy-paste of the help section for "vlookup".

Look at my example, look at the formula in the highlighted cell, then type vlookup into the help search box. That should be plenty to work it out with, and you'll then be filled with a warm, fuzzy, I-worked-it-out feeling.

Which is nice.