VLOOKUP Query in MS Excel

[carlosbourn]

First off, apologies for the cross-posting yesterday.

I am using the following VLOOKUP function to look up matching ports a ship calls at and inserting matching insurance premium rates for calls at that port:

=IF(ISNA(VLOOKUP(C8,Ports!$B$2:$D$51,3,FALSE)),"",(VLOOKUP(C8,Ports!$B$2:$D$51,3,FALSE)))

However, the rates for the ports sometime change frequently week by week. To show this in my workbook I have added additional colums in the 'Ports' sheet specifying the weekly dates i.e. 11/04/08 - 17/04/08 & 18/04/08 - 25/04/08 and so on.

Is there a way I can develop this VLOOKUP formula to incorporate the date the ship made the call so that the correct rate is picked up from the 'Ports' sheet?

In the sheet with the VLOOKUP formula I have a column for the date so I guess I would have to roll 2 VLOOKUPs into one; one which finds the correct rate depending on the port of call, and another which ensures that the correct rate is picked up depending on the date of the call.

Please advise if you require any further clarification!

Thanks

[arthurbr]

You can use a MATCH / INDEX combination like

Please Login or Register  to view this content.

where c8 contains the ship and c9 the date

and to exclude the cells where there is no match, use

Please Login or Register  to view this content.

[carlosbourn]

Thanks for the reply arthurbr.

So this formula finds the corresponding vessel and inserts the rate from the 'Ports' like the VLOOKUP does and matches the date from within the range of dates in the 'Ports' sheet too?

Problem is, the weekly rates in the 'Ports' sheet are shown under dated columns with '11/04/08', '18/04/08', 23/04/08 and so on. So if a ship makes a call on 13/04/08, the rate would be the one under column titled '11/04/08'.

I need to pick up the call date from the input sheet and then lookup in the 'Ports' sheet for the corresponding week commencing date. Is that possible?

Thanks

[arthurbr]

In the MATCH part of the formula pertaining to dates range, replace the 0 with 1. It will then look to the next lowest number if no match is found.
You can post a sample of your data if you like

[carlosbourn]

Ok - have attached a sample.

Of course, the VLOOKUP only searches for one column for the data to return so see why that wouldn't work now.

Thanks for your help.

[arthurbr]

This seems to work .
Don't forget to replace the ; with , if necessary in the formulas

[carlosbourn]

Thanks - works fine with the sample. However I have just incorporated this into the genuine spreadsheet and it doesn't seem to pick up the correct rate?

The formula now reads as below:

Please Login or Register  to view this content.

The rate that should be getting picked up is 0.125%. However am getting 0.075%? I've got a feeling this is to do with how I have formatted the array cells.

Plase see attached for more in-depth sample of the 'Ports' sheet. You will see that I named the LOCATION range from an additional section of locations at the bottom of the sheet rather than the main body of info at the top. Is this why it isn't working?

[arthurbr]

In your formula you use the range named LOCATION to find your rate. I think you need to serach on range $b$3:$b$52 relating to Ports.
Which raises to question to know if two ports can have the same name in two different locations?

On the other hand your table uses a lot of merged cells.
If you ever need their contents for further analysis you might run into trouble.

[carlosbourn]

I thought it might be a problem with all the merged cells - particularly with each location being merged with several cells below. Do you think there is a better way I could arrange this? I didn't really want to repeat the locations line by line.

The merged locations cannot be used for the list validation on the input sheet otherwise it causes the drop-down lists to contain loads of spaces because it treats the merged cells individually, rather than merged.

2 ports cannot have the same name in 2 different locations.

I'll try your suggestion with the array part of the formula now. Thanks

[carlosbourn]

The range '$b$3:$b$52' seems to return a #REF error?

[arthurbr]

Could you post a sample containing the error you got ( there is no "INPUT" sheet in your last attachment)

[carlosbourn]

Here it is.

Thanks for staying with me here!

[arthurbr]

As two ports will not have the same name, I based the match function on port, not on location

[carlosbourn]

Thanks Arthurbr - seems to work fine!

One further question, noticed you have used the IF function this time - is there a way I can incorporate the 'IF(ISNA...' function into the formula so I can paste it down and not get #NA where no data has been entered?

Thanks so much for your help

[arthurbr]

Thanks Arthurbr - seems to work fine!

One further question, noticed you have used the IF function this time - is there a way I can incorporate the 'IF(ISNA...' function into the formula so I can paste it down and not get #NA where no data has been entered?

Thanks so much for your help

Entering the ISNA function is redundant. The first part of the function ( countif) takes care of the" #NA. The ISNA will work but slows down the calculations
You will find explanations here :http://www.ozgrid.com/Excel/stop-na-vlookup.htm

[carlosbourn]

Hi again arthurbr!

The COUNTIF function doesn't seem to get rid of the '#N/A' where no data has been entered yet?

Is there a way around this?

Thanks again