PDA

View Full Version : [SOLVED] VLOOKUP help



Barryj
11-24-2015, 01:21 AM
I received this work book from a colegue which works well, but I wanted to add to the columns on the diagrams page but the VLOOKUP will not read past row 76 in the columns under MONDAY. If I type in 9912 in Monday in the master sheet it's OK, IF I type in 9913 in Monday on the master sheet I get #N/A it's as if there is a restriction on the numbers that can be entered into the cell.

Could someone have a look and advise if this is the case, I have never had this problem before and have googled but not found an answer.

Thanks for any assistance.

mancubus
11-24-2015, 01:43 AM
formulas use static named ranges.

use a row number to include all needed rows
for ex change =Diagrams!$A$3:$D$76 to =Diagrams!$A$3:$D$5000 for Monday in Formulas / Name Manager

or use dynamic ranges...
change =Diagrams!$A$3:$D$76 to =OFFSET(Diagrams!$A$3,0,0,COUNTA(Diagrams!$A:$A)-2,4)) for Monday
change =Diagrams!$M$3:$P$77 to =OFFSET(Diagrams!$M$3,0,0,COUNTA(Diagrams!$M:$M)-2,4)) for Thusday

repeat this for all named ranges using correct sheet names column letters...
make sure there are no blank cells in the first (leftmost) columns of the ranges.


Google "Dynamic Named (Defined) Ranges" for more...

http://www.excel-easy.com/examples/dynamic-named-range.html
http://www.contextures.com/xlNames01.html

Barryj
11-24-2015, 01:55 AM
Thanks for the assistance mancubus, do you know and could you explain why the formula will not reference past row 76, I am just trying to understand this as I have never seen this before!

thanks

mancubus
11-24-2015, 02:03 AM
formula in Master!D4:
=IF(C4="","",VLOOKUP(C4,Monday,2,FALSE))
it is the same as
=IF(C4="","",VLOOKUP(C4,Diagrams!$A$3:$D$76,2,FALSE))

since the lookup value in Master!C4 (9920) is in Diagrams!A84 and this cell is not in the lookup range, formula returns error.

Barryj
11-24-2015, 02:15 AM
mancubus
So how can the current formula range be extended, or how is the current formula restricted to row 76, I understand the formula with the defined range but have never seen this formula, how does it know to lookup only to row 76?

thanks

mancubus
11-24-2015, 02:39 AM
i had described in post #2.

Barryj
11-24-2015, 02:42 AM
Thanks mancubus, didnt understand post at first have re-read it and can see what you have described.

Thanks again.

mancubus
11-24-2015, 03:09 AM
welcome.

pls mark the thread as solved if you are sorted.