Vlookup, offset, match & countif

Colin Legg · #2 10-15-2011, 10:55 AM

Hi Julie,

First, let's try to understand the problem with the formulae by looking at a specific example. Changing the member's name in G3 to Daniel Richman, I'll pick the formula in cell Y168 as an example - this one is pulling pack data for David Saying ADVD02 when you don't want it to be.

The formula is this:

Code:

=IFERROR(VLOOKUP(O$168,
    OFFSET(Membership_No, MATCH(MembNo,Membership_No,0) ,4, COUNTIF(Membership_No,MembNo) ,6)
    ,6,0),"Null")

Let's break it down into it's component parts.

Code:

COUNTIF(Membership_No,MembNo) 
 
returns 7

This is fine - it represents the number of records for that member on the records sheet.

Code:

MATCH(MembNo,Membership_No,0) 
 
returns 201

This is because the Membership_No named range is defined as Records!$B$2:$B$2850.
The first row of data for Daniel Richman begins on row 202, which is the 201st value in B2:B2850.

We can now simplify the OFFSET() part of the formula to this:

Code:

OFFSET(Records!$B$2:$B$2850, 201 ,4, 7 ,6)

The 201 component is the row offset. Let's think about what the row offset returns.

A row offset of 1 from B2:B2850 would return B3:B2851.
A row offset of 2 from B2:B2850 would return B4:B2852.
A row offset of 100 from B2:B2850 would return B102:B2950.
So a row offset of 201 from B2:B2850 would return B203:B3051

So this is where the problem lies in the formula. You want it to return a range starting from row 202 when it is actually returning a range starting from row 203.

Accounting for the other arguments in OFFSET (column/width/height):

Code:

OFFSET(Records!$B$2:$B$2850, 201 ,4, 7 ,6) 
 
Returns a range reference of Records!F203:K209

We can confirm this with a quick check using some VBA:

Code:

Sub CheckRanges() 
 
    Debug.Print [OFFSET(Membership_No,1,0)].Address 'returns B3:B2851 
    Debug.Print [OFFSET(Membership_No,2,0)].Address 'returns B4:B2852 
    Debug.Print [OFFSET(Membership_No,100,0)].Address 'returns B102:B2950 
    Debug.Print [OFFSET(Membership_No,201,0)].Address 'returns B203:B3051 
 
    Debug.Print [OFFSET(Membership_No,201,4,7,6)].Address 'returns F203:K209 
 
End Sub

So the problem is effectively that MATCH() operates off a 1-based index whereas OFFSET() operates off a 0-based index. By that, I mean that if the first cell in a given range is a match, then MATCH() returns 1, whereas to return the first cell in a range using OFFSET() you would use 0.

To compensate for this, you just need to adjust the return value of MATCH() by subtracting 1.

Code:

=IFERROR(VLOOKUP(O$168,
   OFFSET(Membership_No,MATCH(MembNo,Membership_No,0)-1,4,COUNTIF(Membership_No,MembNo),6)
   ,6,0),"Null")

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
using a cell content as a string with COUNTIF	geoffm	Excel	1	08-16-2011 11:31 AM
Countif with 2 criteria	ibrahimaa	Excel	3	05-23-2011 11:23 AM
Vlookup and If conditions together along with match formulas	david_benjamin	Excel	2	04-11-2011 11:30 AM
Problems with Match	Theofficehedgehog	Excel	3	07-26-2009 02:07 PM
Need another formula to match my last one	tinkertron	Excel	2	04-29-2009 02:17 PM