nested if and vlookup functions

[1212]

I have 31 columns (the days of the month) and I need to search in each of those, depending on the current date (ex. AK=1) for specific values like ST1 (ΣΤ1), get the name reffered to this value like Peter.

I can manage this for cases up to 7, because of the if statement limitation (up to 7 nested if's).

I need to use more than 7 nested if statements in the following code

#=IF(AK1=1;VLOOKUP("ΣΤ1";$D$6:$AI$100;33-$D$3;FALSE);IF(AK1=2;VLOOKUP("ΣΤ1";$E$6:$AI$100;33-$E$3;FALSE);IF(AK1=1;VLOOKUP("ΣΤ1";$F$6:$AI$100;33-$F$3;FALSE);IF(AK1=2;VLOOKUP("ΣΤ1";$G$6:$AI$100;33-$G$3;FALSE);IF(AK1=1;VLOOKUP("ΣΤ1";$H$6:$AI$100;33-$H$3;FALSE);IF(AK1=2;VLOOKUP("ΣΤ1";$I$6:$AI$100;33-$I$3;FALSE);IF(AK1=1;VLOOKUP("ΣΤ1";$J$6:$AI$100;33-$J$3;FALSE);VLOOKUP("ΣΤ1";$K$6:$AI$100;33-$K$3;FALSE))))))))#

is there another way do this?

if AK=3 goto column C+3 (that is, col F) and find KP. Post the name that corresponds to KP1 in cell X1.
the same for each other one KP1, St1, AOT2, OD, etc.

Could I use code like
if AK=i then vlookup in D + i columns...?


Unfortunately I know nothing about vba, so please be patient…

checkofRepTasks1.xls
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	01/02/06
2								february
3	Names	1	2	3	4	5	6	7	8	9	10	11	12
4														Names
5	BASILAKIS									3				BASILAKIS
6	DASKALAKISA.				/1	/3								DASKALAKISA.
7	DROUDAKIS													DROUDAKIS
8	KALEMIKIARAKIS	3		1	1	4								KALEMIKIARAKIS
9	KALOMOIRHS	1	2	1	3	2								KALOMOIRHS
10	KANAKARAKIS-KPSMI	3	2	4	2									KANAKARAKIS-KPSMI
2

Also I need help in the following for the same project,

if AK1=3 goto column C+3 and find ΕΞ. Post in X5 the name that corresponds to that name. Post in X6 the city that corresponds to that ΕΞ. and lastly post if the cell in column AX corresponds to number 1, then X5="08.00", else if nu=2 then X5="10.00" etc. Do the same for next ΕΞ, in cells X12, X12 and X12.

if AK1=3 goto column C+3 and find Π..Place in A20 the corresponding name. Check horizontically the letters after Π, for K, Anar, Tim, etc. , and count each one. Then according to the dates of the columns place for K in cell b20 startind date and in b21 ending date. If K is followed by another word ex. Tim do the same for tim in the next 3 cells. In a last cell place the starting date of all and the ending date of all.


I know I ask for too much, but I am really stuck in those for 5 days now and I need to finish with this task so that I can go on with my rest of the work.

Thanks in advance for any help,
1212

 

[Glove_Man]

Okey Dokey!!!

I don't fully understand what you want done. I have a feeling you have been inserting extra columns into your sheet to repeat the names - Don't Do this!!
I am assuming that your 31 days start in cell D3 and move to the right UNBROKEN.

Although it's not always a good idea, I think OFFSET will allow you to do what you want, and without VBA.

Could I use code like
if AK=i then vlookup in D + i columns...?

Yes. If you use OFFSET(c4,0,$AK$1,1000,1) then you will get the 1000x1 range that is ak1 cells to the right of C4... If 1000 isn't enough, just use a bigger number.

Now, I'm not ocmpletely sure how your spreadsheet works, but here goes with the first problem:

=INDEX($C$4:$C$1004,MATCH("ST1",OFFSET($C$4,0,AK$1,1000,1)))

Like algebra, working from the inside out, The OFFSET() selects the range AK1 cells to the right of C4, the MATCH() finds how far down that column the value "ST1" is and then the INDEX() looks that far down column C, thereby returning the name.

Is this what you want to put in cells X1 to X12? I get a little lost there.

If you put the list of values you need to look up in cells W1 to W12 and then replace "ST1" with W1, you can copy the formula down

i.e.:
in X1 write =INDEX($C$4:$C$1004,MATCH(W1,OFFSET($C$4,0,AK$1,1000,1))) and then copy down.

Once you've got that working, post the new layout of the spreadsheet (no duplicate names) and restate the other problems...