Lookup & Index

[Georgia Golfer]

I am creating a spreadsheet for the mortgage department. I have 2 tabs, appraisal fees & construction inspection fees.

Appraisal Fees will be completed by the processor as the applications are received and fees are collected. Column E is to identify loans that are construction loans, marked with a 'Y'.

In the construction tab, I want to bring over columns C & D from appraisal fees when there is a Y in column E of appraisal fees tab. I want to index this so that it starts from the top of the construction inspection fees.

Is it as simple as adding an index function to the start of the if formula?

Please Login or Register  to view this content.

Thanks again, you guys are the experts....Jeff

[JBeaucaire]

Is it as simple as adding an index function to the start of the if formula?

It may be. Post up your workbook (click GO ADVANCED and use the paperclip icon) and make sure you manually mockup your desired results, too, so we see clearly what you're after.

[Georgia Golfer]

Sample added. Thanks again

[Excel Matic]

Georgia,

I could not locate the sample file. Am I missing something?

[Georgia Golfer]

I edited the original post & added it as an attachment.

[JBeaucaire]

Here's how I would do it. I added some values in column H of sheet1 that create an INDEX of incrementing values whenever a "Y" occurs.

On sheet2 I added a column A INDEX that only shows numbers matching the column H values of the prior sheet. Now a simple INDEX/MATCH formula in columns B and C can pull over the values for the row with a "1" in it, then a "2", etc.

Once you're ok with that, you can color those columns font to match the background, making them invisible, if you wish.

[Georgia Golfer]

I like it, but when I copy the formula from the 1st sheet on paper & rewrite it in my spreadsheet, I can't get it to work. It's a simple if statement not requiring an array command, right? It's verbatim from what works in your spreadsheet.

FYI, I'm using vista now, does that make a difference?

[JBeaucaire]

Simple INDEX/MATCH/MAX functions work the same on Vista, so no issue there.

On sheet2, there are 3 distinct formulas:
A1:
=MAX('Appraisal Fees'!H:H)

A2:
=IF(1>A1,"",1)

A3 and then copied down:
=IF(OR(A2="",N(A2)+1>$A$1),"",A2+1)

Make sure you get all those correct.

========
Again, perhaps just working directly with your actual workbook is simplest. Post that up if needed, just sanitize the customer names temporarily and put them back later.

[Georgia Golfer]

You never disappoint. Marked solved and added to your rep....Thanks again bud.