Vlookup problems

danieldrmm

New Member
Joined
Jul 7, 2010
Messages
9
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

New Member
Joined
Jul 7, 2010
Messages
9

ADVERTISEMENT

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
 

danieldrmm

New Member
Joined
Jul 7, 2010
Messages
9
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,631
Messages
5,597,282
Members
414,134
Latest member
Snowfall

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top