LOOKUP and "nesting" IF formula troubles

Afternoon,

The enclosed spreadsheet includes a couple formulas I’m having trouble with.
The questions themselves are detailed in the document.
Any suggestions would be a big help.

See attached for a proposal...

I added a helper column to Workbook 1 (column D)

In D9:

Please Login or Register  to view this content.

copied down

Then in Workbook 2:

In E20:

Please Login or Register  to view this content.

which is confirmed with CTRL+SHIFT+ENTER not just ENTER. Then copied down

In F20:

Please Login or Register  to view this content.

copied down

In G20:

Please Login or Register  to view this content.

copied down

These formulas can be adjusted to suit your "real ranges"

Thanks NBVC. I really appreciate the quick response.

Wow, these formulas are pretty involved. I'm looking forward to spending some time this morning trying to figure out what all this language means. I have never used some of these functions but I’m excited to see if I can figure them out without further explanation. This website has become a great educational tool for me in a short time. Thanks again to all those involved!

Of course there is always the chance I come back with a bunch of questions!

I'll respond once I take a closer look.

Formula # 2:

The first part of the formula: ROWS($E$20:$E20)>SUMPRODUCT(--ISNUMBER($D$9:$D$13)) checks to see if the row number you're in is greater than the number of numeric entries...if it is greater then a blank is inserted (so no errors are visible). Note: I didn't use Countif() because you are working with separate workbooks and Countif() doesn't work on closed workbooks.

The next part: INDEX($E$9:$E$13,SMALL(IF(ISNUMBER($D$9:$D$13),ROW($E$9:$E$13)-ROW($E$9)+1),ROWS($E$20:$E20)))

is like a vlookup for multiple matches... It uses the Index() function which requires you to index the table or column to extract from and it requires the Row number to extract from which is gotten from this part: SMALL(IF(ISNUMBER($D$9:$D$13),ROW($E$9:$E$13)-ROW($E$9)+1),ROWS($E$20:$E20))

The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if a number is found in range $D$9:$D$13 and then it will return the corresponding row number within the range. The -ROW($E$9)+1 is added for robustness (incase you insert rows above, then the result won't skew).

The last ROWS($E$20:$E20) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA.

The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.

More on Array Formulas here.

Then you copy down... The only thing that changes copying down is the ROWS($E$20:$E20) in the 2 occurances within the formula.. again to determine the step or row number we're in.

Formula 3 & 4:

Index and Match is like using Vlookup or Vlookup and Hlookup together to find an intersection point in a table...where the lookup values are the Row and/or Column headers... the syntax of Index() is INDEX(Table,Row_number,Column_Number). The Row Number can be found by using the MATCH() function.. which looks up an item in a vertical range and returns the vertical position. The Column_number too can use the Match() function to lookup an item in a horizontal range and return a position.. these two positions act as intersection points and returns the value at that intersection in the indexed table... In this case I just embedded another Index/Match function to find the column position.... Note that the column_number argument (or row_number) is optional if you select a column only or row only as the indexed table... as I did when determining the column_number.

The IF() function was simply used to return a blank if there is nothing in E20 down... otherwise you would get an #N/A error instead of a clean looking blank.

Hope this helps.

I noticed that the last set of entries in "workbook 1" are in row 14...

... you formula in E20 reflects that, but none of the other formulas in E21 down and in F20 (down and across) reflect that... instead they reflect a range ending at row 13 in "Workbook 1".

Is that the issue?