Loop + vLookup cause formula too long

Dear All,

I really need help, thanks in advance first.

Following is part of my formula ( still have more):

=IF(C4="A",VLOOKUP(A4,'Emp List'!A:B,2,0),"")&IF(C4="B",VLOOKUP(A4,'Emp List'!A:C,3,0),"")&IF(C4="C",VLOOKUP(A4,'Emp List'!A:D,4,0),"")&IF(C4="D",VLOOKUP(A4,'Emp List'!A:E,5,0),"")&IF(C4="E",VLOOKUP(A4,'Emp List'!A:F,6,0),"")&IF(C4="F",VLOOKUP(A4,'Emp List'!A:G,7,0),"")&IF(C4="G",VLOOKUP(A4,'Emp List'!A:H,8,0),"")&IF(C4="H",VLOOKUP(A4,'Emp List'!A:I,9,0),"")&IF(C4="I",VLOOKUP(A4,'Emp List'!A:J,10,0),"")&IF(C4="J",VLOOKUP(A4,'Emp List'!A:K,11,0),"")&IF(C4="K",VLOOKUP(A4,'Emp List'!A:L,12,0),"")&IF(C4="L",VLOOKUP(A4,'Emp List'!A:M,13,0),"")&IF(C4="M",VLOOKUP(A4,'Emp List'!A:N,14,0),"")&IF(C4="N",VLOOKUP(A4,'Emp List'!A:O,15,0),"")&IF(C4="O",VLOOKUP(A4,'Emp List'!A:P,16,0),"")&IF(C4="P",VLOOKUP(A4,'Emp List'!A:Q,17,0),"")&IF(C4="Q",VLOOKUP(A4,'Emp List'!A:R,18,0),"")&IF(C4="R",VLOOKUP(A4,'Emp List'!A:S,19,0),"")

I tried nested condition, but only can up to 7 levels, hence i try this formula but cause my formula too long.

Is there a way to shorten it?

The working is C4 is the drop down list, and If C4 = a, then will use A vlookup formula, if C4 is B , then will use B vlookup formula and etc.

Pls try to help`~

Many Many Many Tks

Asyura

Hi, Asyura. Try this much shorter version of the formula:

=VLOOKUP(A4,INDIRECT("'Emp List'!A:"&C4),COLUMNS(INDIRECT("A:"&C4)),0)

HTH

Jason

Is the content of cell A4 the column headings of 'Emp List'?

Yes.

If i key in the staff id in A4, it will do the vlookup to Emp List.

If A4 = ABC and C4 = Surname,
It will vlookup A4 = ABC and display the surname.

If A4 = ABC and C4 = Given Name
It will vlookup A4 = ABC and display the Given Name

Ok, try this one. Before, I was using your example of A, B, C, etc. to denote the columns. Since you have data in here that matches the column headings, you can use MATCH to determine the column number. So try this one, and let me know how it works:

=VLOOKUP(A4,INDIRECT("'Emp List'!C1:C"&MATCH(C4,'Emp List'!A1:S1,0),0),MATCH(C4,'Emp List'!A1:S1,0))

HTH

Jason

Yes Yes..

Jason, it's workable... Many Many Tks.

But i have another problem again. =p

Same Logic but instead of using vlookup, i need to define the drop down list.

For example if C4 = "Gender", E4 is a drop down list with value {Male, Female}
If C4 = "Marital Status", E4 is a drop down list with Value {Single, Married, Divorced} and etc.

My working is, i define a name for Male and Female as Gender.
After that i use validation and create a list with source = Gender.

So the condition is gonna set at Source.
But If.. Else condition only can up to 7 level.
Hence, i switch to concantenate or "&" method, but seem it prompt me the error msg that, "Source have error"

If there a way to define such a way or i should use macro?

No need for INDIRECT or IF.

=VLOOKUP(A4,'Emp List'!A:B,CODE(C4)-CODE("A")+2,0)

Thanks Norie, but seem it's not workable for my spreadsheet.

Now my another problem is the validation list. How am i going to put the conditions at validation list's source.

Hi, Asyura.

See if this link works for you:

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

HTH

Jason