# Vlookup and if/then?

#### googs

##### Board Regular
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
ABCDEFGHIJKL
11412119876532
2Scout1313119777552
3Scout1013139777552
4Scout8131111777522
5Scout8131177755
6Scout6131111777522
7Scout613117755
8Scout413117755
Sheet1

Worksheet 2:
Book1
ABCDEFG
1ScoutShtDefHandsRebAthl
2A.J.Dance13675127
3AaronMcLain1065958
4AbdulAvant13581199
5AbdulFast8778812
6AbdulPope463635
7AbdullahStalder888766
Sheet2

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
ABCDEFGHIJKL
1ScoutShtDefHandsRebAthl
2A.J.Dance13675127775137
3Aaron McLain106595875957
4Abdul Avant13581199571199
5Abdul Fast8778812777711
6Abdul Pope46363575757
7Abdullah Stalder88876677777
Sheet2

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

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.

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

Replies
16
Views
527
Replies
3
Views
229
Replies
1
Views
297
Replies
0
Views
95
Replies
1
Views
143

1,203,663
Messages
6,056,626
Members
444,879
Latest member
suzndush

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back