# 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

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### mrshl9898

##### Well-known Member
Try

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

#### gtd526

##### Active Member
Try

=IF(AND(IFERROR(MATCH(J2,'Weekly Picks'!A4:A35,0),0)>0,IFERROR(MATCH("ml",'Weekly Picks'!AX8:AX35,0),0)>0)),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$C\$35,3,0),VLOOKUP(J2,'Weekly Picks'!\$A\$4:\$D\$35,4,0))
I entered the formula, but received an error. "problem with this formula"

#### mrshl9898

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

apologies

#### 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
With my results.
It seems everything is True.
It should be 2 True, 2 False.
"ml" is only contained in 2 cells, the rest are blank.

#### mrshl9898

##### Well-known Member
Try breaking it down.

What are these returning?

IFERROR(MATCH(J2,'Weekly Picks'!A4:A35,0),0)
IFERROR(MATCH("ml",'Weekly Picks'!A4:A35,0),0)

#### gtd526

##### Active Member

With my results.
It seems everything is True.
It should be 2 True, 2 False.
"ml" is only contained in 2 cells, the rest are blank.
I did a COUNTIF-
=COUNTIF('Weekly Picks'!\$AX\$4:\$AX\$35,"ml")
results = 2

#### mrshl9898

##### Well-known Member
Well

MATCH("ml",'Weekly Picks'!AX8:AX35,0) should return #N/A error if there's no "ml", so IFERROR(MATCH("ml",'Weekly Picks'!AX8:AX35,0),0) should return 0

0 is not greater than 0 so it should be IF(AND(TRUE,FALSE) which is FALSE.

It's hard to debug if you don't want to break the formula down.

#### gtd526

##### Active Member
Try breaking it down.

What are these returning?

IFERROR(MATCH(J2,'Weekly Picks'!A4:A35,0),0)
IFERROR(MATCH("ml",'Weekly Picks'!A4:A35,0),0)
Try breaking it down.

What are these returning?

IFERROR(MATCH(J2,'Weekly Picks'!A4:A35,0),0)
IFERROR(MATCH("ml",'Weekly Picks'!A4:A35,0),0)
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

#### mrshl9898

##### Well-known Member
IFERROR(MATCH(J2,'Weekly Picks'!A4:A35,0),0) = 27
IFERROR(MATCH("ml",'Weekly Picks'!AX4:AX35,0),0) = 4

So both are TRUE what do they return on the ones you expect to be FALSE?

Replies
4
Views
50
Replies
18
Views
186
Replies
5
Views
162
Replies
1
Views
33
Replies
37
Views
1K

1,127,180
Messages
5,623,216
Members
415,957
Latest member
Newguy1924

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