Vlookup formula

[David Ryan]

Hi i have a spreadsheet that uses this lookup table that lives on a sheet
called "Times".
on the working sheet if CHC is entered then Am is displayed in the relevant
cell and if ED NW the ND is displayed and so on.

my formula =IF(ISERROR(MATCH('Hotel
Services'!C8,Times!$A$2:$A$21,0)),"",LOOKUP('Hotel
Services'!C8,Times!$A$2:$D$21)) seems to work but not sure if it is robust?

When I try to identify the column number to get the data out of in the
formula (in this case column 4), i get an error message. the formula as is
seems to work so if there is no column identified in the lookup formula does
it assume its the last column? and will this give consistant accurate results?

Shift Shift Time Hours AM/PM
CHC 06:00 to 14:30 8 AM
CW 06:00 to 14:30 8 AM
ED NW 22:00 to 06:30 8 ND
ED WARDS 06:00 to 14:30 8 AM
EVENING W 14:00 to 22:30 8 PM

hope someone can spare the time to assist with this.

 

[Squeaky]

HI David,

If I am following you right....
You titled your note Vlookup but then used Lookup in your formula.

Instead of Lookup use Vlookup, such as

=VLOOKUP('Hotel Services'!C8,(Times!$A$2:$D$21),4,false)

This will look up the contents of 'Hotel Services'!C8, in the range starting
in A2 to D21, look for a matching entry in the first colum of the range, A2,
then take the entry that is in the same row in the 3rd column of the range,
in this case the D column. (If you need the 5th column you need the range to
be A1:E1, etc.) The range parameters must encompass the column you need to
draw data from.

According to how LOOKUP works, If LOOKUP can't find the lookup_value, it
uses the largest value in the array that is less than or equal to
lookup_value. That may not be correct for your needs.

So in your example the lookup portion would look like:

=IF(ISERROR(MATCH('Hotel Services'!C8,Times!$A$2:$A$21,0)),"",VLOOKUP('Hotel
Services'!C8,(Times!$A$2:$D$21),4,false)

This will work if 'Hotel Services'!C8 can be found in the A column of Times
between 2 and 21, and the result you want is in the D column same row.

Hope I'm not too confusing.

Squeaky

 

[David Ryan]

Hi Squeaky

This has worked well thanks for your time.