match text from two books, and a word from second book and return value from 4th column

**RobinOfLocksley** · 08-28-2017, 06:34 PM

I have a rather difficult request, or at least difficult for me to figure out. I was able to do the index match to pull the initials based on matching the ID numbers, but need it to also Match the word "Active". If the word ACTIVE isn't there, an IFERROR statement, to put "Company Expense".

The information is in two workbooks.

First workbook has ID numbers(columnA), and column I (where I want the formula)

Second workbook has ID (columnA) and whether the account is active or inactive(ColumnH). Also has client initials(columnB).

I need a formula in book1 that looks at the ID in both books, and looks in columnH of second book for the word "Active", then returns columnB(Initials) to book one in columnI.

All help is greatly appreciated.

**Glenn Kennedy** · 08-29-2017, 02:50 AM

Next time, please attach a sample worksheet. Normally I never want to spend time recreating what you already have in front of you. With both sheets open, this array formula:

=IFERROR(INDEX('[Copy FROM.xlsx]Sheet1'!$B$2:$B$7,MATCH(1,('[Copy FROM.xlsx]Sheet1'!$A$2:$A$7=A2)*('[Copy FROM.xlsx]Sheet1'!$H$2:$H$7="Active"),0)),"")

When the file is closed, the formula becomes:

=IFERROR(INDEX('C:\Users\DGlenn\Desktop\[Copy FROM.xlsx]Sheet1'!$B$2:$B$7,MATCH(1,('C:\Users\DGlenn\Desktop\[Copy FROM.xlsx]Sheet1'!$A$2:$A$7=A2)*('C:\Users\DGlenn\Desktop\[Copy FROM.xlsx]Sheet1'!$H$2:$H$7="Active"),0)),"")

showing the FULL path down from C:\ to the source file.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

**RobinOfLocksley** · 08-29-2017, 10:59 AM

First, I apologize for making you create the sheets yourself.

Second, THANK YOU SO VERY MUCH! Worked perfectly.

**AliGW** · 08-29-2017, 11:02 AM

Was this an Outlook or an Excel query???

match text from two books, and a word from second book and return value from 4th column

LinkBack

Thread Tools

Rate This Thread

Display

match text from two books, and a word from second book and return value from 4th column

Re: match text from two books, and a word from second book and return value from 4th colum

Re: match text from two books, and a word from second book and return value from 4th colum

Re: match text from two books, and a word from second book and return value from 4th colum

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need to import from Multiple excel books into Master book based on one column

VBA - search books for value, return row to current book - please help

book or books for Excel for Mac 2011

open books,ink cells and then save books and move on to the next book

[SOLVED] Two Excel sheets trying to match text data and return the match from the second column!

[SOLVED] Simple formula to match column A, sheet1, with column A, sheet2, return text answer

Find and Match Text in Column Return Adjacent Cell

Bookmarks

Bookmarks

Posting Permissions