# Match and Match, then vLookup on True, vLookup on False

#### gtd526

##### Active Member
Hello,
IF
Cell Value(J2) = Cell Value in a Range(A4:A35) AND
Cell Value("ml") = Cell Value in a Range(AX8:AX35), if True, then vlookup, if False, then vlookup.

Here's the formula I have:
=IF(AND(MATCH(J2,'Weekly Picks'!A4:A35,0),(MATCH("ml",'Weekly Picks'!AX8:AX35,0))),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$C\$35,3,0),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$D\$35,4,0))

thank you

#### mrshl9898

##### Well-known Member
Maybe it's easier or helpful to explain the first issue you had also.

=IF(AND(MATCH(J2,'Weekly Picks'!A4:A35,0),(MATCH("ml",'Weekly Picks'!AX8:AX35,0))),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$C\$35,3,0),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$D\$35,4,0))

Would be

=IF(AND(27,4),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$C\$35,3,0),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$D\$35,4,0))

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### gtd526

##### Active Member
IFERROR(MATCH(J2,'Weekly Picks'!A4:A35,0),0) = 27
IFERROR(MATCH("ml",'Weekly Picks'!AX4:AX35,0),0) = 4
the bold above is changed to original.
J2="LVR", which will change when scrolled down. The highlighted Purple is the list for J2:J5 range.

Here is what Im working with:

NFL.xlsm
ABCDAX
4ATL2.5
5CAR-2.5
6NE4
7BUF-4WLml
8TEN-6.5WLml
9CIN6.5
10LVR2.5WL
11CLE-2.5
12IND-3
13DET3
14MIN7.5
15GB-7.5
16NYJ19.5
17KC-19.5
18LAR-4
19MIA4
20
21
22NO-4
23CHI4
24SF3
25SEA-3
26DAL
27PHI
28TB-11.5
29NYG11.5
30LAC-3WL
31DEN3
32
33
34
35
Weekly Picks
Here's where the formula will be. L:L

NFL.xlsm
JKLM
1MeATS*W/L
2LVR2.5W7
3LAC-3W4
4BUF-4W
5TEN-6.5W
6
7
8   2
ESPNvsANvsMe
Cell Formulas
RangeFormula
J2:J8J2=IFERROR(LOOKUP(2, 1/((COUNTIF(\$J\$2:J2, 'Weekly Picks'!\$J\$4:\$J\$35)=0)*( 'Weekly Picks'!\$J\$4:\$J\$35<>"")), 'Weekly Picks'!\$J\$4:\$J\$35),"")
K2:K8K2=IFERROR(VLOOKUP(\$J2,'Weekly Picks'!\$A\$4:\$B\$35,2,0),"")
L2:L5L2=IF(AND(IFERROR(MATCH(J2,'Weekly Picks'!\$A\$4:\$A\$35,0),0)>0,IFERROR(MATCH("ml",'Weekly Picks'!\$AX\$4:\$AX\$35,0),0)>0),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$C\$35,3,0),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$D\$35,4,0))
M2M2=IFERROR(MATCH(J2,'Weekly Picks'!A4:A35,0),0)
M3M3=IFERROR(MATCH("ml",'Weekly Picks'!AX4:AX35,0),0)
L6:L8L6=IFERROR(VLOOKUP(\$J6,'Weekly Picks'!\$A\$4:\$D\$35,4,0),"")
M8M8=COUNTIF('Weekly Picks'!\$AX\$4:\$AX\$35,"ml")

#### mrshl9898

##### Well-known Member
I see, so you need ml to line up against the team?

=IF(VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$AX\$35,50,0)="ml",VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$C\$35,3,0),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$D\$35,4,0))

It was just saying TRUE team found TRUE ml found, not ml found against team.

#### gtd526

##### Active Member
IFERROR(MATCH(J2,'Weekly Picks'!A4:A35,0),0) = 27
IFERROR(MATCH("ml",'Weekly Picks'!AX4:AX35,0),0) = 4
the bold above is changed to original.
J2="LVR", which will change when scrolled down. The highlighted Purple is the list for J2:J5 range.

Here is what Im working with:

NFL.xlsm
ABCDAX
4ATL2.5
5CAR-2.5
6NE4
7BUF-4WLml
8TEN-6.5WLml
9CIN6.5
10LVR2.5WL
11CLE-2.5
12IND-3
13DET3
14MIN7.5
15GB-7.5
16NYJ19.5
17KC-19.5
18LAR-4
19MIA4
20
21
22NO-4
23CHI4
24SF3
25SEA-3
26DAL
27PHI
28TB-11.5
29NYG11.5
30LAC-3WL
31DEN3
32
33
34
35
Weekly Picks

#### gtd526

##### Active Member
I see, so you need ml to line up against the team?

=IF(VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$AX\$35,50,0)="ml",VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$C\$35,3,0),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$D\$35,4,0))

It was just saying TRUE team found TRUE ml found, not ml found against team.
YES. And it works. I was just explaining what I was looking for in a reply, but I was replying to myself I had to exit the reply, then saw this reply,Thank god.
I will try to explain much better in the future. Maybe it was to vauge. Sorry about that. I realize what I'm trying to do is beyond the beginner level.
THANK YOU!
Now I have to figure out how you came up with that formula.

Replies
4
Views
51
Replies
18
Views
212
Replies
5
Views
172
Replies
2
Views
48
Replies
37
Views
1K

1,128,180
Messages
5,629,188
Members
416,370
Latest member
Lgathana

### 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