The following formula provides another way to display in Workbook1 the text in some row of six cells in Workbook2, if cell A1 in Workbook2 equals some value "X".
It is an array formula, so you would have to select the six cells in Workbook1 where you want to display the text values, paste or type the formula into the formula bar, and then complete the entry with Ctrl+Shift+Enter. The formula will show dashes ("-") in the cells if there is no "X" in cell A1 of Workbook2. (For simplicity, I assume the values in both workbooks are in Sheet1.)
The key part of the formula is the OFFSET function, which takes the following arguments:
- A cell reference, in this case $B$1
- A row offset, the number of rows up (a negative number) or rows down (a positive number) that the range of cells you want to return begins
- A column offset, the number of columns left (negative) or right (positive) that the range you want to return begins
- The height, or number of rows in the range that you want to return
- The width, or number of columns in the range that you want to return.
In the formula, the cell reference is $B$1, the row offset from $B$1 is 0, the column offset from $B$1 is 0, the height of the values to be returned is 1 row, and the width is 6 columns. Here, the OFFSET function will return the values in cells B1:G1 if "X" is a match. Had the row offset been 2 and the column offset 4, the values in cells F3:K3 would be returned.