Vlookup and if/then?

[googs]

Ok, this may not even be possible or be too confusing so I hope I word this correctly. Basically, this is for a basketball game. I want to take the information given on the second worksheet to input results based on the data it contains under its columns Sht, Def, Hands, Reb, and Athl. I want the number in the "scout" column to be looked up in worksheet 1 under the B2-B8 cells. When it matches up, I want the other numbers in the row starting with the number under "Sht" to the number under "Ahtl" to match the number from the C1-L1 cells. The intersected result (between the scout number and the number under the stat) should produce a number. For example, let's say the scout is 13 and under "Sht" it has the number 6. It would go to the 13 row (B2:L2) and be under the I1 column (because it contains the number 6). The result would produce the number 7 in H2 on worksheet 2. I want that number to go next to H2-L7 with the Sht numbers in column H, Def numbers in column I, etc.

It gets trickier. The reason I have 2 8s and 2 6s under worksheet 1 for the scouting rows is because the first 8 and the first 6 is what I call highs and the second 8 and 6 are lows. I want it so if the scout says 8 it should always go to the high row (B4-L4) unless ANY stat contains a 9. I also want it so if the scout says 6 it should always be considered high (row B6-L6) unless it has a 7 under ANY stat. For example, let's say the scout is 8 and no stat for the recruit contains a 9. This would be considered a "high" 8 because there is no 9 in any of the stats. However, if the scout was 8 and under "Sht" and "Def" it contained a 9, then it would look at the low 8 scout (row B5:L5)

I hope this makes a lick of sense. If you have ANY questions please ask me. Seems like it will need a vlookup with multiple if/then statements? Not sure. Pretty new to excel.







Worksheet 1:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1			14	12	11	9	8	7	6	5	3	2
2	Scout	13		13	11	9	7	7	7	5	5	2
3	Scout	10		13	13	9	7	7	7	5	5	2
4	Scout	8	13	11	11		7	7	7	5	2	2
5	Scout	8				13	11	7	7	7	5	5
6	Scout	6	13	11	11	7	7		7	5	2	2
7	Scout	6					13	11	7	7	5	5
8	Scout	4					13	11	7	7	5	5
Sheet1

Worksheet 2:

Book1
	A	B	C	D	E	F	G
1		Scout	Sht	Def	Hands	Reb	Athl
2	A.J.Dance	13	6	7	5	12	7
3	AaronMcLain	10	6	5	9	5	8
4	AbdulAvant	13	5	8	11	9	9
5	AbdulFast	8	7	7	8	8	12
6	AbdulPope	4	6	3	6	3	5
7	AbdullahStalder	8	8	8	7	6	6
Sheet2

 

[fairwinds]

Hi,

This is a little hard to interpret.

Here is my guess:

=INDEX(Sheet1!$C$2:$L$8,MATCH($B2,Sheet1!$B$2:$B$8,0)+($B2=8)*ISNUMBER(MATCH(9,$C2:$G2,0))+($B2=6)*ISNUMBER(MATCH(7,$C2:$G2,0)),MATCH(C2,Sheet1!$C$1:$L$1,0))

in H2. Drag right / down.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Scout	Sht	Def	Hands	Reb	Athl
2	A.J.Dance	13	6	7	5	12	7	7	7	5	13	7
3	Aaron McLain	10	6	5	9	5	8	7	5	9	5	7
4	Abdul Avant	13	5	8	11	9	9	5	7	11	9	9
5	Abdul Fast	8	7	7	8	8	12	7	7	7	7	11
6	Abdul Pope	4	6	3	6	3	5	7	5	7	5	7
7	Abdullah Stalder	8	8	8	7	6	6	7	7	7	7	7
Sheet2

 

[googs]

Sorry for the late response...but what you did seems right. I tried in my workbook and it did not seem to work unfortunately. My workbook is bigger and a little different than the sheets I showed...well actually the only difference is that there are more players in sheet 2 and their stats are in different cells than what I listed...so I tried to arrange it to fit as needed to no avail. Can I maybe e-mail you my workbook so you can get a better feel?

Thanks a whole lot

 

[fairwinds]

Then you should not need to re-arrange. just adjust the references in the formula. If you think it is hard, take it apart and try to make the different parts work in different cells. That is also a good way of understand how it actually works. Did you get it to work on the sample data?, Try thast first, then adjust to your actual data.

 

[googs]

Ok phew I feel dumb...I was doing one little thing wrong but fixed it...I gotta tell you that it is exactly what I was looking for and really did not think anyone understood my problem...so BIG THANK YOU! I owe you one.

Thanks again