drop down list formula's

**davittp** · 07-31-2007, 04:49 PM

Hi,

I am having a little trouble finding out how to link two columns for a drop down list.

I have Column A1 - A20 populated which links to a drop down list in cell C1. I want cell C2 to be populated with the contents of the corresponding B column.

For example, if in the drop down list, Cell A2 is selected, i want cell C2 to be populated with the contents of B2. If A5 is selected it should be B5.

I have tried using a number of IF commands, (=IF(C1=A1,B1,IF(C1=A2, B2, IF(C1=A3,b3... ETC...

But this can only handle up to A8 (8 IF commands) before the formula stops working.

Does anyone know of a way that i can link cells A and B to show that whichever number A is used, the correct B cell will be displayed?

Thanks

**oldchippy** · 07-31-2007, 05:23 PM

Hello davittp,

Welcome to the forum

Try this in D1

=VLOOKUP(C1,A1:B20,2,FALSE)

http://www.contextures.com/xlFunctions02.html

**davittp** · 08-01-2007, 05:51 AM

Thanks for the code.

this works perfectly.

Could you advise on how this works? or what each part means?

Many Thanks

**oldchippy** · 08-01-2007, 05:58 AM

Originally Posted by davittp

Thanks for the code.

this works perfectly.

Could you advise on how this works? or what each part means?

Many Thanks

It's a VLOOKUP function, searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

C2 is the cell you wish to look up, A1:B20 is the range to find it in, 2 is the column where the information you want returned is found, FALSE is an exact match.

Take a look under Excel Help and type in VLOOKUP more explaination there.