Vlookup with multiple IF statements

[SemperFi]

Hello
I am working on an Excel knowledge base where I have a formula (=IF(ROW(A1)>$B$2; ""; INDEX(Articles!$A:$A; MATCH(ROW(A1); Articles!$B:$B; 0))) ) that is working perfectly to pull a "list" of articles from the article sheet. The problem I'm running into is setting up a Vlookup with multiple IF statements. Let me explain:
Next to each article returned I have a check box, I'm using this check box "true" condition to return the written article if checked, if not checked then it should look at the row below and so on. Please see the images below. I'm also open to a VBA code if anyone can come up with it.

Please note: in my sheet only 1 check mark can be selected at a time, not multiple selections.

This is the formula I'm using "Note" the semicolon ";" is a must due to the region I'm working from (Latin America), a comma won't work.

This is the first Vlookup that works perfect, it see the check mark "true" condition in C4 and looks up B4 returning the data stored in the article sheet.

Sample below pertains to C5 amd C6 checked "true" but returns a " 0 ".

I really hope you guys can help me with this issue. I appreciate your time and all comments are welcome.

 

[Alex Blakenburg]

Your last IF statement is either not required or needs an Else condition.
Option 1) Cater for all 3 being false

=IF(C5=TRUE,VLOOKUP(B5,Articles!$M$7:$O$11,3,FALSE),
      IF(C6=TRUE,VLOOKUP(B6,Articles!$M$7:$O$11,3,FALSE),
          IF(C7=TRUE,VLOOKUP(B7,Articles!$M$7:$O$11,3,FALSE),
           "")))

Option 2) Assume there will always be at least one True

=IF(C5=TRUE,VLOOKUP(B5,Articles!$M$7:$O$11,3,FALSE),
         IF(C6=TRUE,VLOOKUP(B6,Articles!$M$7:$O$11,3,FALSE),
                               VLOOKUP(B7,Articles!$M$7:$O$11,3,FALSE)))

This would be another option.
Note: Since you are using TRUE/FALSE for the check box C5=TRUE is the same as just putting C5

=IFERROR(
                    VLOOKUP(
                                       IF(C5,B5,IF(C6,B6,B7)),
                                       Articles!$M$7:$O$11,3,FALSE),
     "")

 

[SemperFi]

Hey Alex thank you so much for taking time out to help me with this issue.
Unfortunately none of the formulas worked; option 1 and 2 I get an N/A as a result and option 3 (IFERROR), I get a blank return. I placed an image of each three formulas. Also, note that due to regional issues my Excel won't accept commas in the IF formula I have to replace the comma for a semicolon.
Thank you


Option 1

Option 2

=IFERROR

 

[Alex Blakenburg]

I am having to retype your original formula from a picture and with no XL2BB data.
I can see you had to change my C5 to C4 etc but you probably also need to change all my
$M7:$O11 to $A2:$M9.
The quickest to change is my last one.

If that does not work please send me a text copy if your original formula.

 

[SemperFi]

I am having to retype your original formula from a picture and with no XL2BB data.
I can see you had to change my C5 to C4 etc but you probably also need to change all my
$M7:$O11 to $A2:$M9.
The quickest to change is my last one.

If that does not work please send me a text copy if your original formula.

Thank you Alex, will go ahead and do the changes. I'll let you know.

 

[SemperFi]

Hi Alex,
I installed the addin to share a mini-sheet. Please know this is the first time I use it, so hopefully I did it right.

Thank you

StringSearchTool (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Search:	dog
2	Results	3
3					IF([D4:D6],(VLOOKUP(C4:C6;Articles!C2:N4;2;0),"")))
4	Articles	Dog's are a man's best friend		FALSE
5		In the dog house		FALSE
6		Low down dirty dog		FALSE
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Search

Cell Formulas
Range	Formula
B2	B2	=MAX(Articles!B:B)
C4:C6	C4	=VLOOKUP(B4,Articles!A2:C9,3,0)
B4:B18	B4	=IF(ROW(A1)>$B$2, "", INDEX(Articles!$A:$A, MATCH(ROW(A1), Articles!$B:$B, 0)))

 

[Alex Blakenburg]

Thanks
Your mini sheet shows you have vlookups in C4:C6.
Where is your check box True/False value going ?

 

[SemperFi]

They are located in D4:D6, for some reason they did not show up, but when you hover on it you can see the "false" callout.

 

[Alex Blakenburg]

If you are still online can you do an xl2bb of the lookup on the articles sheet as well.

 

[Alex Blakenburg]

The data you are showing me has a lot of inconsistencies so I don't know which columns to actually use.
So the True / False is actually in Column D (previously shown as column C), I have changed it to column D.
The formula in E3 is showing

• Use column C as the lookup and I can't see any values in there
  I will use column C - you will need to change that if it should be B on your sheet.
• You previously indicated that you wanted to look up from A2:M9 the formula in E3 indicates C2:N4
  I will use C2:N4 - you will need to change that if it should be something different.

Since this is the easiest formula to modify start with this one.

=IFERROR(
                    VLOOKUP(
                                       IF($D$4=TRUE;$C$4;
                                             IF($D$5=TRUE;$C$5;
                                                         IF($D$6=TRUE;$C$6;
                                                                 "XXDummyXX")));
                                       Articles!$C$2:$N$4;2;FALSE);
     "")