VBA Lookup

**TYork** · 12-30-2009, 11:37 AM

I'm trying to write script that basically act as a lookup. I think I need to use the "find" and "offset" functions with a loop and an if statement to stop it when the list is finished. I cannot use a general non vba lookup function because of the way the data is displayed.

Goal: Determine the "Average FTE Based on Section Count" based on the Client

Any help would be greatly appreciate.

**romperstomper** · 12-30-2009, 11:46 AM

Are you actually using Excel 2008 as your profile suggests?

**TYork** · 12-30-2009, 11:48 AM

I use 2007, sorry. I need to change that.

**romperstomper** · 12-30-2009, 12:04 PM

If your layout is always the same, you could use this formula in C3:
=IF(ISNA(MATCH(B3,'STAFF ANALYSIS'!F:F,0)),INDEX('STAFF ANALYSIS'!G:G,MATCH(B3,'STAFF ANALYSIS'!G:G,0)+6),INDEX('STAFF ANALYSIS'!F:F,MATCH(B3,'STAFF ANALYSIS'!F:F,0)+6))
and copy it down.

**TYork** · 12-30-2009, 12:18 PM

That would work for the example I presented but there are instances in the actuals where it can go 20 columns deep. Even though I use 2007 it would need to be compatible with 2003 because that is what most in my office use.

**romperstomper** · 12-30-2009, 12:35 PM

How's this?

**TYork** · 12-30-2009, 01:01 PM

This is perfect and a lot more efficient than what I had drawn up in pseudocode. I appreciate the help as I try to build up my scripting abilities. Thanks.