Share via

Adjusting the range in VLOOKUP based on MATCH

Anonymous
2017-09-04T03:15:41+00:00

I have a long column of data with a sequential number in a cell followed by several rows with text followed by the next number and more rows of text (Col B).  The number of text rows can vary from 8 to 30 or more.

Something like this:

Cell B1 = 1

Cell B2 thru 9 = text rows

Cell B10 = 2

Cell B11 thru 26 = text rows

etc.

I am extracting the relevant content of the text into the cells in the row adjacent to the number.  Some rows are in a consistent position such as name, address, etc. but others are less so.  In column A i use TYPE() formulas and other text checks to categorize each row and use VLOOKUP to pull the text into the horizontal data row.

One of the categories is 5 so VLOOKUP pulls the value in col B where 5 is in col A.

The problem is that not every group has a 5 category entry and because of the variable number of text rows a simple VLOOKUP array that looks in the maximum number of rows that might occur in a group can end up extracting the value from the next group if there is no category 5 in a group with minimum entries.

I am open to suggestions on how to find a solution but the one i have in mind is to use a formula to calculate the array in VLOOKUP based on the start of the next group.  The start of each group is identified with a value of 10 in col A so i can test for the size of the group with the MATCH formula to look for the next 10 and that will tell me how many rows in the group.

What i can't seem to get to is what formula to use in the second part of the VLOOKUP array to limit the VLOOKUP search to the full range of the group it is targeting , and no further.

I've futzed around with MATCH, and INDEX and OFFSET but so far no luck.

I realize this might be a little confusing so here is a more concrete example (fewer than actual # of text rows in the example for brevity).

Any help greatly appreciated.

Thanks 

Ed

VLOOKUP(5,A1:B16,2,0) fails because group 7 happens to not have a type 5 row of text so it coninues and takes the wrong value from group 8.

What's needed is a formula like VLOOKUP(5,A1:xxx,2,0) where the formula xxx determines how many text rows occur until the next row with a number in col B and thus a 10 in column A and limits the end of the arracy to that length.  In the example the result of xxx would be A5 or VLOOKUP(5,A1:A5,2,0) for group 7 (which understandably would result in #N/A) and A15 or VLOOKUP(5,A6:A15,2,0) for group 8.

Row   Col A            Col B

1           10                   7

2            2                    text type 2 

3            2                    text type 2 

4            2                    text type 2 

5           10                    8

6            2                    text type 2 

7            2                    text type 2 

8            2                    text type 2 

9            2                    text type 2 

10          2                    text type 2

11          2                    text type 2

12          2                    text type 2

13          2                    text type 2

14          5                    text type 5 

15           10                  9 

16          2                    text type 2

The VLOOKUP looking for the type 5 text in group 7 finds the type 5 text in group 8 instead

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2017-09-04T10:59:41+00:00

    Thanks Katrina,

    Came to this forum from an old link (www.microsoft.com/office/community/en-us/default.mspx) which then redirected me to here via https://answers.microsoft.com/en-us/msoffice.  Apparently things have changed.  Is there a description somewhere of what happened and what's what for future questions?

    As far as this question, after some more web searching (including *"table_array element"*in the search helped, without that i kept getting answers about array formulas) and fiddling about, i came up with this solution that works.

    For Group 7 = VLOOKUP(5,A2:OFFSET(A2,MATCH(10,A2:A30,0),0),2,0)

    For Group 8 = VLOOKUP(5,A6:OFFSET(A6,MATCH(10,A6:A36,0),0),2,0)

    The first table_array element (A2, A7) in both the VLOOKUP and MATCH functions and the 2nd in the Match function (A30, A36) is relative and changes automatically as you move down the sheet.

    Interestingly, unless I am mistaken, in this configuration, even though, using group 8 as an example;

              MATCH(10,A6:A36,0) resolves to 9 and so

              OFFSET(A6,MATCH(10,A6:A36,0) or OFFSET(A6,9,0) resolves to 10

    Thus the expected resulting VLOOKUP formula would be 

              VLOOKUP(5,A6:10,0),2,0)

    which would result in an error, but it actually calculates using A16 instead of 10 or

              VLOOKUP(5,A6:A16,0),2,0) 

    Somehow Excel knows that the intent in this context is for OFFSET to provide the resulting cell location and not the value in the resulting cell.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-09-05T11:19:21+00:00

    The Excel community is a forum site wherein our experts are focusing on Excel queries thus, we advised you to post this query there.

    Thank you for providing us the exact formulas needed for your concern. It would greatly help the community members who might be having the same query as yours.

    Please don't hesitate to post back if you have further questions.

    0 comments
  2. Anonymous
    2017-09-04T06:54:54+00:00

    Hi,

    Your Microsoft Excel question is more complex than what is typically answered in the Microsoft Answers forums. It would be best to post your query in the Microsoft Excel Community forum. We have experts there that can definitely answer your query.

    You may post it here.

    Feel free to post back should you need further assistance.

    0 comments