Lookup = 0 pass row 31

[blacky1]

Hi all new at this.

we put a lookup in our workbook but when we use row 31 is displays 0. Works well upto row 30 but when you use row 31 is displays "0"

=LOOKUP(F6,'Switch Boards'!A5:A31,'Switch Boards'!C5:C31)

Any ideas on how to make lookup work past row 31?

[arthurbr]

Hi and welcome to the board

Are you using relative or absolute references for the lookup and result vector ?
In the first case, the vectors will change when the formula is dragged down

Try converting to absolute references =LOOKUP(F6,'Switch Boards'!$A$5:$A$31,'Switch Boards'!$C$5:$C$31)

[blacky1]

Hi and thanks for the speed responce. Not sure if it is relative or absolute but this is what we do.

We go to "formulas/Look up & Reference/LOOKUP/Lookup_Value, Lookup_vector, result_vector" then add the values.

We tried your reference by copy and paste but bid not work.

Are we doing it correct? or should we be doing it in another way?

We have noticed that it is erratic and it may be related to our ref we are using.

In the "Switch Boards'!A5:A31" we have this

MSITP002
MSITM003
MSICR005
MSICR006
MSICA007
MSICR008
MSICG009
MSITP010
MSITP011
MSIT2011
MSITP012
MSIT2012
MSITP013
MSITP014
MSIT2014
MSIT2015
MSITP016
MSIT2016
MSITP017
MSIT2017
MSIT2018
MSICG019
MSICG020
MSICD021
MSICD022
MSITD023
1

if we change it to

1
2
3
4
and so on it works. Hmmmmm

any ideas?

[arthurbr]

Better post a sample sheet with what you have and what you need. This will make it easier to solve

[blacky1]

Here is a sample of what I am using.

If you look at "AC Equipment List" G6 to G14, this is the look up. If you select in F6 to F14 this should change G6 to G14 to match what is in "Switch Boards" row C. If we change the numbers in "Switch Boards" A5 to A80 to read 1, 2, 3, 4 and so on it works. So looks like it is related to the numbers we are using.........hmmmmm......though this would not matter

[scottylad2]

index and match seems to fix

=INDEX('Switch Boards'!$C$5:$C$80,MATCH('AC Equipment List'!F6,'Switch Boards'!$A$5:$A$80,0))

[trucker10]

embedded file
VLOOKUP

[blacky1]

Yes thank you that did the trick .

1 more questions.

Question: You guy seam to use "$"..... example "Switch Boards'!$C$5:$C$80" rather than "Switch Boards'!C5:C80". I notice it works ether way but is there some reason we should use the "$"......we are using 2010

[Marcol]

Dollar signs, $, are "anchors", this is what Arthur meant by Relative and Absolute Cell References.

See this link
http://www.ozgrid.com/Excel/free-tra...n-16-basic.htm

Cheers

[blacky1]

Ahhhh.......haaaaaa......ok, thanks for your help everyone......much appreciated . Have a great day.