+ Reply to Thread
Results 1 to 4 of 4

Adapt formula to search multiple columns on different sheets

  1. #1
    Registered User
    Join Date
    12-06-2016
    Location
    Poole
    MS-Off Ver
    2010
    Posts
    78

    Adapt formula to search multiple columns on different sheets

    Hello everyone

    Simple one. I have this formula (=IFERROR(IFERROR(INDEX(Sheet3!B:B,MATCH($C$2,Sheet3!G:G,0)),INDEX(Sheet3!B:B,MATCH($C$2,Sheet3!H:H,0))),"") ) that I have been using successfully for some time now.

    My question is, how can I adapt the formula instead of searching for the value in C2 in Sheet3!B:B to search a different workbook. All i need is to adapt the formula to search 2 different columns on 2 different sheets (for ex: Sheet1 columns B and C and sheet2 columns E and F).

    =IFERROR(IFERROR(INDEX('[Car Parking applications database 2017 vers.3.xlsm]Single Occupancy'!$B$3:$B$471,MATCH($C$2,Sheet3!G:G,0)),INDEX(Sheet3!B:B,MATCH($C$2,Sheet3!H:H,0))),"") - this is as far as I could get but I don't know how to add another sheet of the same document after this [Car Parking applications database 2017 vers.3.xlsm]Single Occupancy'!$B$3:$B$471

    I hope it makes sens.

    Cheers

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Adapt formula to search multiple columns on different sheets

    This doesnt make sense.
    You say
    " instead of searching for the value in C2 in Sheet3!B:B"
    but in the new formula afterwards you have

    =IFERROR(IFERROR(INDEX('[Car Parking applications database 2017 vers.3.xlsm]Single Occupancy'!$B$3:$B$471,MATCH($C$2,Sheet3!G:G,0)),INDEX(Sheet3!B:B,MATCH($C$2,Sheet3!H:H,0))),"")

    which clearly still includes a search of Sheet3 column B

    So what are you trying to do?
    Add 4 searches of the Car Parking workbook for columns Sheet1!B and C and Sheet2!E and F to the existing formula or replace the existing 2 searches with the 4 searches from the car parking workbook?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    12-06-2016
    Location
    Poole
    MS-Off Ver
    2010
    Posts
    78

    Re: Adapt formula to search multiple columns on different sheets

    Hello

    I need the formula to search the database attached, search under all columns that have registration no. in them, and return all the details that we have under the searched reg.

    I have attached an example of the database to get an idea on what I am trying to achieve.

    All i want, in a nutshell, is to adapt the formula to search the database document instead of sheet3 of the registration search.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-06-2016
    Location
    Poole
    MS-Off Ver
    2010
    Posts
    78

    Re: Adapt formula to search multiple columns on different sheets

    Anyone able to help with my above request?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 06-24-2015, 09:08 AM
  2. Possible adapt macro to write sum formula (variable sheets) instead of result?
    By Kamolga in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2015, 10:28 AM
  3. [SOLVED] Search Columns from Multiple Sheets If Match Populate Value from 1 of the sheets
    By kellyjo7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-08-2014, 05:52 PM
  4. Replies: 6
    Last Post: 08-22-2014, 08:47 AM
  5. Replies: 8
    Last Post: 11-20-2013, 12:21 AM
  6. [SOLVED] How to adapt the macro to allow it to consolidate files with multiple sheets?
    By billj in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-04-2013, 05:18 PM
  7. Replies: 1
    Last Post: 02-24-2005, 06:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1