I have a combo box from which I select a name from a range, 'Destination' in the adjacent column to the range C9:C93 is supplementary information that I wish to display when a 'Destination' is selected. I have tried =IF(C2="","",VLOOKUP(C9:C93,2,FALSE)) but I get a value error.

Am I using the wrong type of look up and/or is the formula completely wrong for the task?

I have a combo box from which I select a name from a range, 'Destination' in the adjacent column to the range C9:C93 is supplementary information that I wish to display when a 'Destination' is selected. I have tried =IF(C2="","",VLOOKUP(C9:C93,2,FALSE)) but I get a value error.

Am I using the wrong type of look up and/or is the formula completely wrong for the task?

TIA[/quote]

I just tried using INDEX and that doesn't work either =INDEX(C9:C93,$C2) but I get the value error?

[quote name='mbarron' post='792630' date='09-Sep-2009 04:17']If this is your entire formula, then you are missing the vllokup value.
=IF(C2="","",VLOOKUP(C9:C93,2,FALSE))

try this instead

=IF(C2="","",VLOOKUP(C2,C9:C93,2,FALSE))[/quote]

Hi M Barron

Thanks for the response but that is still showing the value error, I have made another column and numbered it 1 - 93 and then in my list range in the combo I have changed it to B9:B93 and then used the INDEX function =INDEX(D993,$D2) and that now works but with a limitation which is that the number now shows with the destination in the combo, for example

5 Germany

and when I select it E2 now shows the desired result Berlin, Colgne, Dusseldorf etc. but the Combo is just showing the number 5, ideally I would like itto show just Germany but if it has to show 5 Germany I can livewith it. Any ideas what I need to change in the Combo properties to reflect this?

Thanks for the response but that is still showing the value error, I have made another column and numbered it 1 - 93 and then in my list range in the combo I have changed it to B9:B93 and then used the INDEX function =INDEX(D993,$D2) and that now works but with a limitation which is that the number now shows with the destination in the combo, for example

5 Germany

and when I select it E2 now shows the desired result Berlin, Colgne, Dusseldorf etc. but the Combo is just showing the number 5, ideally I would like itto show just Germany but if it has to show 5 Germany I can livewith it. Any ideas what I need to change in the Combo properties to reflect this?

TIA[/quote]
Doh

Got it 'Column Widths' first 1 set to 0 (hides the number), second set to 2 shows the Country 0,2