Lookup between sheets

[SloopJohnB]

I am having problems to get my lookup formula to work

=LOOKUP($M27,('sheet 1'!$G:$G),('Sheet1'!$AC:$AC))

On this Sheet2 in S27 I am after the value from Sheet1 column AC that matches cell Sheet2 M27 that matches the data in Sheet1 column G.

I hope this makes sense.

Thanks in advance.

[Moo the Dog]

You could try (obviously untested):

=INDEX('Sheet1'!$AC:$AC,MATCH($M27,'Sheet1'!$G:$G,0))

- Moo

[Max, Singapore]

Think you could try this ...
In Sheet2,
In S27: =INDEX(Sheet1!AC:AC,MATCH(M27,Sheet1!G:G,0))
--------------------------------
Any good? Wave it, whack the little star at the bottom left of my responses

[SloopJohnB]

Thanks guys.

For my only interests.

Whats the difference between Lookup and Index?

What does the 0 do at the end.

Also if there is not data in the look up column AC, can I show a blank cell in S27?

[Max, Singapore]

> Whats the difference between Lookup and Index?
I don't use Lookup but you could always check up Excel help for the differences

Here's my views ..
The expression Index/Match is in function similar to Vlookup. I prefer using Index/Match as it allows one to retrieve stuff to the left or right of the Match col, unlike Vlookup. Also Index/Match avoids the "hassle" with the column index num reference required in Vlookup. For eg its much easier to just copy across Index/Match to have the returns from consecutive columns. Using Vlookup, you would need to count/increment the column index num from the first column of the table/range

> What does the 0 do at the end
The zero in MATCH(M27,Sheet1!G:G,0)
specifies the MATCH to look for an exact match for M27 within Sheet1!G:G
--------------------------------
Any worth? Hit the little star at the bottom left of my responses

[FDibbins]

@ Max, just to add in to what you said..."Using Vlookup, you would need to count/increment the column index num from the first column of the table/range "...you could use MATCH() to automate that part for you too. VLOOKUP() needs a column number - MATCH() returns a number - so you could use it to find the *matching* column heading for that column reference

[Max, Singapore]

@FDibbins
.... If you re-read the views expressed, it was a plain vanilla to vanilla comparison of the 2 functions, and viz a vis formulae propagation. Your add on would be amongst sophistications, which could come later after OP grasps the fundamentals

[Max, Singapore]

> Also if there is not data in the look up column AC, can I show a blank cell in S27?
A simple error trap could be used, indicatively like this:
=IF(Index/Match=0,"",Index/Match)

[Tony Valko]

Another one...

If the formula normally returns a TEXT value:

=T(lookup_formula_here)