+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

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

    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.
    Attached Files Attached Files
    Last edited by wfidler; 10-05-2012 at 09:50 PM. Reason: provide more details

  2. #2
    Registered User
    Join Date
    10-05-2012
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

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

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

  3. #3
    Registered User
    Join Date
    10-05-2012
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

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

    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.

  4. #4
    Registered User
    Join Date
    10-05-2012
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

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

    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?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06291 seconds
  • Memory Usage 8,919KB
  • Queries Executed 15 (?)
More Information
Template Usage (32):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (4)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (2)postbit_attachment
  • (4)postbit_legacy
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (48):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1