One formula using IF; ISERROR; INDEX; OFFSET; MATCH.

The following formula isn't working. Is it because the lookup value is not to the far left in that sheet?? The formula cell show a blank.

There's two sheets.

The below formulas are in Column G.
1. The formula needs to find/match from another sheet ('Financial Summary' sheet Col H), the value in Col D which is on the same sheet/row as the formula).

2. If the above value (in Fin Sumry Col H) is found, then from Fin Sumry Col O, the first cell showing a value in a row below that is entered.

3. Generally the lookup value in Col O will be between 1 and 10 rows below the found value in Col H.

There's 20 formulas (Col G; in rows 8 to 27) matching/finding values in the Fin Sumry sheet over rows 4 to 70.

Hope you can understand all that...

=IF(ISERROR(INDEX('Financial Summary'!$O$1:$O$103,MIN(IF(OFFSET('Financial Summary'!$H$1,MATCH($D14,'Financial Summary'!$O$1:$O$103,0)-1,7,100,1)<>"",1,999999999999)*(ROW(OFFSET('Financial Summary'!$H$1,MATCH($D14,'Financial Summary'!$O$1:$O$103,0)-1,7,100,1)))))),"",INDEX('Financial Summary'!$O$1:$O$103,MIN(IF(OFFSET('Financial Summary'!$H$1,MATCH($D14,'Financial Summary'!$O$1:$O$103,0)-1,7,100,1)<>"",1,999999999999)*(ROW(OFFSET('Financial Summary'!$H$1,MATCH($D14,'Financial Summary'!$O$1:$O$103,0)-1,7,100,1))))))

Thnx. Pls see it attached...

You could modify your data on the financial summary sheet by

• manually moving the Land Cost data up one row so that the total is not on the same line as "Legal/Loan Costs"

• get rid of the blanks in column H (Find/Replace > GoTo Special > Blanks and then apply the formula
  

  Please Login or Register  to view this content.

  to the active cell with Ctrl + Enter).

• copy the following formula into E SII!G8 and copy down:

Please Login or Register  to view this content.

Let me know if you have any questions.

Thnx Guys!! I will go thru your advice and will report back when I have fixed to let you know...

Hi J

1. Thnx I didn't realise land Cost total was on the same line as Legal/...
2. I'm sorry but I am having probs following the second instruction from you.
-I have Excel 2013 so the tab is called Find & Select
-I've tried hi-liting the whole of Col H and then tried hi-liting just the blank three relevant to 1.04 and then applied the formula =H3. But neither works. You need to advise what cells in Col H I need to pick before I do do the Find, etc.

Thnx ND

I did that extra column and then I hid it.

I highlighted H3:H75. The formula can be written:

Please Login or Register  to view this content.

Your can see the results of modifying the Financial Summary sheet and applying the formula to the E S II sheet in the file:

Copy of Stewart St 10 Lots.xlsx

Let me know if you have any questions.