SemperFi

New Member
Joined
Aug 21, 2014
Messages
18
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.
001.JPG


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.
002.JPG


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

004.JPG


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

SemperFi

New Member
Joined
Aug 21, 2014
Messages
18
Hi Alex,
My apologies for not answering right away.
Yes, I made a second excel sheet with an additional row, and by mistake I sent you the wrong one. The only difference is the new one has an additional row with a vlookup in an effort from my end to see if it would be easier to call out an id vs. a full sentence would be easier. Let me try your last code. Unfortunately I'm using right now a work computer that has sharing capabilities and download blocked.
Thank you for your help, I really appreciate it.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

SemperFi

New Member
Joined
Aug 21, 2014
Messages
18
Hi Alex
thank you for getting back to me. you're right I sent the wrong excel. The one you got has an additional column in my effort to try to find a solution. I did used the formula you sent last and it worked only for the first check mark. See image below:

Note: Column C is actually pulling an article id, located in the article sheet column see (article sheet screenshot below)
Column D is a simple if statement when check box is marked I get a 1
Both columns c and d are not needed, this is just me trying to find a solution, thus removing it is not an issue

First check box - checked
1628266127378.png


Second and third box gets the same result

1628266212998.png



This is the Article data
1628266337717.png
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,027
Office Version
  1. 365
Platform
  1. Windows
To me it looks like it is working.
If the 2nd checkbox is true, it uses 1204 to do a lookup. Column D in the mapping table is empty. so it returns a zero.
Similarly for checkbox 3, it uses 1206 which ia also blank in column D and also returns a zero.
Try putting a value in the mapping table in column D for 1204 & 1206
 
Solution

SemperFi

New Member
Joined
Aug 21, 2014
Messages
18
How did I miss that!
You know what you are - BRILLIANT!
Thank you so much Alex without you this task would have been impossible. Thank you a million, just brilliant man 🙏🙏.
Pedro
Semper Fi
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,027
Office Version
  1. 365
Platform
  1. Windows
LOL. Where I live we are in lockdown at the moment, your feedback may well be the highlight of my day. :)
 

SemperFi

New Member
Joined
Aug 21, 2014
Messages
18
I'm glad to hear that, you deserve it! Stay safe and remember lockdown is only for a while - dead is forever! Take care of yourselve.
 

Forum statistics

Threads
1,143,907
Messages
5,721,445
Members
422,363
Latest member
Bogus_Potatoes

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top