+ Reply to Thread
Results 1 to 2 of 2

A problem about Indirect(Address()) and Match

  1. #1
    Registered User
    Join Date
    04-09-2023
    Location
    Chorng-Chinq
    MS-Off Ver
    I don't know?
    Posts
    1

    Question A problem about Indirect(Address()) and Match

    In the sheet "blah1", the A3 is "apple", and the B3 is the price "$1". In another sheet "finding", I want to find all prices of foods in the column F, so I write
    "=INDEX('blah1'!$B$1:$B$10000,MATCH(F2,'blah1'!$A$1:$A$10000,0))"
    in G2.

    But I have a lot of sheets blah2 blah3 and so on. So the column B in the sheet "finding" there are the names of those sheets. I want to change the "blah1" in the above formula to "Indirect(Address(1,2,1))", but the formula
    "=INDEX(Indirect(Address(1,2,1))!$B$1:$B$10000,MATCH(F2,Indirect(Address(1,2,1))!$A$1:$A$10000,0))"
    is wrong. I don't know where is wrong and how to fix it...

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,831

    Re: A problem about Indirect(Address()) and Match

    Without seeing a sample sheet, maybe this will help. Let's say in sheet "finding" you have the list of sheet names in cells A1:A10 (or to whatever). You could try this maybe:

    =INDEX(INDIRECT("'"&A1&"'!B1:$B$10000"),MATCH($F$2,INDIRECT("'"&A1&"'!a1:$a$10000"),0))

+ 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. using indirect(address(match())) in the offset function
    By redwar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2018, 03:03 PM
  2. INDIRECT problem with INDEX,MATCH
    By Westy226 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-16-2014, 11:49 AM
  3. [SOLVED] Address Match and Indirect formula
    By mahershams in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 11-19-2013, 02:26 AM
  4. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 PM
  5. Index Indirect Match problem
    By SAsplin in forum Excel General
    Replies: 8
    Last Post: 06-10-2011, 10:08 AM
  6. Indirect Address Match - search value based of 2 variables
    By arazoe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-09-2010, 06:04 PM
  7. Indirect address/match
    By Prcntrygrl in forum Excel General
    Replies: 4
    Last Post: 08-26-2009, 05:05 PM

Tags for this Thread

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