Lookup a Bible Verse Using #Excel (w/o VBA) by David Hager

John Walkenbach was the first to create an Excel application that allowed the user to read Bible verses.

http://spreadsheetpage.com/index.php/file/the_bible_in_excel/

He used an approach where the entire Bible was loaded into a workbook. I decided to try using a new technique that did not require the Bible verses be part of the workbook.

But, to do this, I needed a source of the number of verses in each Bible chapter. Luckily, I found someone that had already performed that task.

https://c2it.wordpress.com/2011/10/20/table-of-bible-books-chapters-verses/

After pulling in the table from this web site with Power Query and fixing a minor Text to Columns issue, I needed to unpivot the data. In order to do this, I used this Power Query technique on the table.

https://support.office.com/en-us/article/Unpivot-columns-Power-Query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

It required highlighting 149 columns to unpivot, which is the most I have ever heard of. Armed with a three column table with books, chapters and number of verses, I was ready to build the worksheet input cells. I created an unique list of Bible books from column A into column E by utilizing Data Advanced Filter with the unique option. I also made a list of numbers from 1 to 150 (the maximum number of chapters in any Bible book). I added a Data Validation list to cell H1 (named TheBook) using the following defined name range.

BibleBook =BibleChapterInfo!$E$2:$E$67

The next step was to create a dynamic defined name range for chaapter numbers corresponding to each book. A Data Validation list was added to cell I1 with the formula shown below.

TheChapters =INDIRECT(“N2:N”&INDEX(Chapters,MATCH(TheBook,BibleBook,0))+1)

Finally, a Data Validation list was added to cell J1 with the formula shown below.

TheVerses= INDIRECT(“N2:N”&INDIRECT(“C”&MATCH(TheBook&TheChapter,BibleChapterInfo!$A$1:$A$1198&BibleChapterInfo!$B$1:$B$1198,0))+1)

where cells H1:J1 are named TheBook, TheChapter and TheVerse respectively (see figure).

 

While these formulas are very useful and powerful, a discussion of how they work is outside of the focus of this article, which is to use the WEBSERVICE and FILTERXML functions to return the desired Bible verse without use of VBA code.

All of this preliminary work was done in the hope that an XML source for Bible verses could be found. And, the following site was identified as providing this.

http://labs.bible.org/api_web_service

So, the following formula does all of the heavy work in retrieving the XML output and reading the correct node.

In cell H4 =FILTERXML(WEBSERVICE(“http://labs.bible.org/api/?passage=”&TheBook&” “&TheChapter&”:”&TheVerse&”&type=xml”),”//text”

As originally designed, this formula was intended to be in cell H5 (the Bible verse cell). Well, it turned out that some of the verses returned by the web service had a topic attached, as shown below for the value for Song of Solomon 2:3.

<b>The Beloved about Her Lover:</b> Like an apple tree among the trees of the forest, so is my beloved among the young men. I delight to sit in his shade, and his fruit is sweet to my taste.

And so, the following formula removes this XLM part from the string if it is present.

In cell H5 =IF(ISERROR(FIND(“/b>”,H4)),H4,MID(H4,FIND(“/b>”,H4)+3,255))

I hope that you find the techniques demonstrated here useful.

You can download the example file here.

BibleVerse