Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column

**wfidler** · 10-05-2012, 02:29 PM

Hello Everyone!

Background Information:
I get updates for SKU information on a daily basis (see worksheet - Changes_List). However, the information does not match my data record layout. I create a separate worksheet (Unique_Chgs) to build the update record for each SKU. I have figured out how to identify the unique SKU records (viewing other posts in the forum - thanks!), but I am having difficulty trying to find ALL the updates for each SKU.

Problem
The worksheet (Unique_Chgs) I created to build SKU update record currently is using the function VLOOKUP to find the different information (ADDRESS, BRAND, DESCRIPTION, etc.) that needs to be updated for the SKU. I created a column H (heading - Trimmed) in the "Changes_List" worksheet because I found there was a hidden "non-breaking space” in the values in column D (titled - Changed Field).

I used the following formula to remove the "non-breaking space" in the value so I can successfully find the change field in a VLOOKUP:

Cell Ref Cell Formula
H2:H23 TRIM(CLEAN(SUBSTITUTE(D2, CHAR(160),"")))

where D2 referrences the value in Changed Field column.

Below is the formula I have coded in column E (title - Addr) in the Unique_Chgs worksheet.

=IF(C2<>"", IF(VLOOKUP(C2,Changes_List!A:G,7, FALSE)="ADDRESS", TRIM(VLOOKUP(C2,Changes_List!A:G,5, FALSE)), ""), "")

In the first part of the VLOOKUP formula I us column 7 (title - Trimmed) which is the sanitized value of the Field Changes. The second part of the VLOOKUP formula does not use the condition statement ="ADDRESS". Therefore the formula only works for the first occurance of a SKU change. The formula works fine if there is only one update/change for a SKU.

I use the same VLOOKUP in column F (title - Brand) to locate the "BRAND" changes for a SKU. However, since the VLOOKUP function already found a corresponding row previously the function returns "blank" for the "BRAND" changes.

My ideal results for the first SKU (01-0107260) record in the Unique_Chgs worksheet would look like this:

Col C Col E Col F Col G Col H ....
01-0107260 Largo, FL 33777 ANS International 12 Per case - 12 Fl Oz (355ml) ......

From reading other posts, I know there is a way to solve what I am trying to achieve using the INDEX and Match functions, but I am not familiar using these functions and not sure how to reference the cells and ranges to provide the results I am seeking.

I have attached the workbook if anyone would be interested in showing me how to achieve this task. i would greatly appreciate any thoughts or suggestions from you "guru's" out there. Thanks for your assistance.

**wfidler** · 10-05-2012, 10:18 PM

Anyone have any thoughts or suggestions on how achieve the desired results?

**wfidler** · 10-06-2012, 06:43 PM

WOW, not much help from the group this time? Oh well, perhaps next time.

I figured out the solution, it works perfectly. I have tried to explain the best I can on how the formula works. Below are the steps and formulas I used to solve the problem if anyone is interested. The formula below only defines the formula for the cell E2 in the Unique_Chgs worksheet. This is an "array function) and you will need to enter CTRL+SHIFT+ENTER after entering the formula in the cell. You will also need to change the text value from "ADDRESS" to "BRAND" in the next cell F2. This last step will need to be repeated for each of the remaining cells G2 - X2. Once the formulas have been entered on Row 2, then you can copy the cell formulas down the remaining rows 3 - 22.

1. In the Changes_List worksheet I defined three (3) Name Ranges because it is less confusing for me and I can expand the ranges easier without having to change the formulas in the future.

(a) SKU_List ==> which is the range $A$2:$A$22
(b) Change_Type ==> which is the range $G2:$G$22
(c) New_Value ==> which is the range $E$2:$E$22

2. I removed the VLOOKUP function and used a combination of the INDEX & MATCH functions

=INDEX(New_Value, MATCH(Unique_Chgs!C2,IF(Change_Type="ADDRESS",SKU_List),0))

3. Here is the explanation for the MATCH statement: MATCH(lookup value, lookup_array, match_type)
MATCH(Unique_Chgs!C2,IF(Change_Type="ADDRESS",SKU_List),0)

(a) "lookup_value" is equal to the SKU number (Unique_Chgs!C2)
(b) "lookup_array" is equal to the NAMED RANGE "Change_Type" ($G2:$G$22) is equal to "ADDRESS" and
SKU number (Unique_Chgs!C2) ==> "01-0107260" is found in the "SKU_List ($A$2:$A$22).
(c) "match_type" is equal to (0). Only the row where both conditions are TRUE will be returned

4. The INDEX statement only needs to know the Row and Col where the MATCH statement is true to get the final result.

=INDEX(New_Value, MATCH(Unique_Chgs!C2,IF(Change_Type="ADDRESS",SKU_List),0))

(a) the NAMED RANGE "New_Value" is the range of data where the MATCH formula will point the INDEX function to get the data.

I am not sure if I explained the solution as well as the guru's on the site, but I hope it helps others.

**wfidler** · 10-06-2012, 07:04 PM

I am not sure if anyone would be interested in seeing the final solution, but I updated the workbook just in case. I hope it helps?

Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column

LinkBack

Thread Tools

Rate This Thread

Display

Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column

Re: Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column

Re: Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column

Re: Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions