Vlookup problems

[danieldrmm]

Hi there,

I am new to using Vlookup (and in fact more complex excel formula). I am producing a door schedule at an architects practice.
I have set up a sheet within the excel file that has a generic door type code with additional information across several columns. I want to transfer the data from this sheet into a main door schedule that only has the code for each door as the input data (so I want to transfer the other data fields into each adjacent column).

the formula I am using is:

=VLOOKUP(K9,'STANDARD DOOR SCHEDULE'!$A$5:$A$89,1,FALSE)

This works and finds the correct door type from. However, as soon as I want to reference the data from the adjacent columns (by putting 2,3,4 etc as the col_index_number) the formula breaks, and I get a #REF! error.

Does anyone know why?

Thanks
Daniel

 

[VoG]

It is because you have specified a single column as the lookup table. Try something like

=VLOOKUP(K9,'STANDARD DOOR SCHEDULE'!$A$5:$F$89,1,FALSE)

 

[p45cal]

The table you are looking up is only one column wide:
$A$5:$A$89
vlookup looks things up in the leftmost column of a table and returns info from a column number you specify. The table has to be at least that wide.
Change it to:
$A$5:$G$89
(G is chosen at random here!)

 

[danieldrmm]

Perfect, problem solved,
thanks, greatly appreciated!

 

[danieldrmm]

I am now struggling to make the formula work throughout the spreadsheet and wondered if anyone was aware why it may be hit or miss?

=VLOOKUP(CONCATENATE(F35,G35,H35),'STANDARD DOOR SCHEDULE'!$A$5:$AZ$89,5,FALSE)

Basically, it works for certain doortypes and not others
for example it works for a D06,1,A (Concatenated to be D061A) but not a D01,1,A (Concatenated to be D011A). Could it be to do with the values of the concatenated door types? I'm at a bit of a loss.

Thanks

 

[VoG]

There may be spaces in one value and not another. See if this helps http://www.contextures.com/xlFunctions02.html#Trouble

 

[danieldrmm]

Thanks,
I went through and re-entered the figures and it seems to have sorted the problems, there must have been spaces in some data fields.

thanks again, I was looking for hours.