Excel Index Formula

**Ralath** · 07-08-2011, 01:20 AM

Ahhh... And you need to put in the CTRL+SHIFT+ENTER when you have matrices.

**Hessah** · 07-08-2011, 01:23 AM

I think the LOOKUP formula requires you to sort the column in alphabetical order isnt it?

And that's for looking up a specific text?? We want it to show the last cell of a column...

**Ralath** · 07-08-2011, 01:26 AM

Originally Posted by Hessah

I think the LOOKUP formula requires you to sort the column in alphabetical order isnt it?

I think you don't have to if you put a 0 at the end (tells it to be an exact match).

Quote:

And that's for looking up a specific text?? We want it to show the last cell of a column...

You can change the last column to be whatever you want it to be. I just put in text for an example.

**Blaaaaaaaah** · 07-08-2011, 01:46 AM

this thread just reminded me I had a problem with the vlookup formula a while ago and I wanted to ask on FF but I completely forgot

eeeeeeeeeeeeeeeek

**Hraesvelg** · 07-08-2011, 01:49 AM

I'm disappointed in the Excel HHOOKUP command. Never returns what I want it to.

**Blaaaaaaaah** · 07-08-2011, 01:53 AM

asdfdfhjgfjgh LLOOL

**Ralath** · 07-08-2011, 02:05 AM

Originally Posted by Blaaaaaaaah

this thread just reminded me I had a problem with the vlookup formula a while ago and I wanted to ask on FF but I completely forgot

eeeeeeeeeeeeeeeek

The 0 at the end usually fixes most problems. 8/10 times.

**Blaaaaaaaah** · 07-08-2011, 02:29 AM

It doesn't work.

I have two spreadsheets:

1) client details (e.g. Postcode in Column A)
2) suburb/region details with two columns: column A - Region, column B - Postcode

I'm trying to find out what region the clients are from by matching their postcode from sheet 1 to the list of postcode/regions in sheet 2.

But my formular looks like this

=VLOOKUP(Table1[[#This Row],[Mailing Post Code]],Post,2,FALSE)

Replaced false with 0 but didn't work either.

Edit: "Post" is the name of sheet 2

**Blaaaaaaaah** · 07-08-2011, 02:34 AM

I went into a new spreadsheet and took away all the table stuff so it's just raw data:

=VLOOKUP(G2,Sheet2!A1

450,3,FALSE)

Still get #N/A though.

I went to show calculation steps and apparently the error occurs with G2 - locating the postcode in the first place.

waii

**Ralath** · 07-08-2011, 02:38 AM

AHHHH I hate when I can't look at the actual spreadsheets. @_@

Ok.

So. I remember why I had to use my INDEX function now. LOL. But I don't you'll need to hear if you can manipulate your data.

In your "2) suburb/region details with two columns" spreadsheet, you need to switch Column A and Column B.

=VLOOKUP(Table1[[#This Row],[Mailing Post Code]],Post,1,FALSE)

Change that to 1 and see if it works?