I have a problem not sure how to solve it. I want to create a formula which will display the content of the table based on cell value.

I have for e.g two tables Box1, Box2

Box1    Box2

Name1   Name1
Name2   Name2
Name3   Name3

I defined the table name Box1 and Box2, now I would like to achieve something like this

=IF(A1="text",Box1,no match)

When I type in cell A1 text will display me the whole table content in column B

Thank you for all help.

  • a) What version of Excel are you running? b) Does this have to work on other versions? If so, what is the earliest? c) Do you want to show the contents of the Box2 table? If so, how? If not, why was it even mentioned? – Jeeped Feb 12 at 1:09
  • 1
    How did you manage to define the Table names as Box1 and Box2. Those represent cell addresses in Excel 2010, and would usually be flagged as invalid names. – Ron Rosenfeld Feb 12 at 1:15
  • @RonRosenfeld - Excel 2007 as well IIRC. – Jeeped Feb 12 at 3:05
  • @Jeeped. Yes, true. He added an excel-2010 tag while I was creating my comment, so I restricted my comment to that version. – Ron Rosenfeld Feb 12 at 12:00

I don't know how you managed to name your Table Box1 since that should have been flagged as illegal (since BOX1 is a cell address in Excel-2010).

Assuming that statement was incorrect, a formula could be:

B1: =IFERROR(IF($A$1="text", INDEX(_Box1,ROWS($1:1)),""),"")

and fill down as far as needed.

  • 1
    Ha! I didn't catch the BOX1 cell reference conflict until you pointed it out. Good catch. – Jeeped Feb 12 at 3:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.