+ Reply to Thread
Results 1 to 5 of 5

Need Help with Match and index function please.

Hybrid View

  1. 09-29-2024, 01:51 PM #1
    km117
    km117 is offline
    Registered User
    Join Date
    08-31-2013
    Location
    brampton, canada
    MS-Off Ver
    Excel 2019
    Posts
    41

    Need Help with Match and index function please.

    1) Table with columns A, B, C and D with,
    Cell A1 having a text Product,
    Cell A2 having a text Cot12,
    Cell A3 having a text Cot4,
    Cell A4 having a text CGC,
    Cell B1 having a text Pro1,
    Cell B2 having a number 1000,
    Cell C1 having a text Pro2,
    Cell C3 having a number 1500,
    Cell D1 having a text Pro3.,
    Cell D4 having a number 500.
    2) Another table with Column Q, R and S with
    Cell Q1 having a text PROC,
    Cell Q2 having a text Pro1,
    Cell Q3 having a text Pro1,
    Cell Q4 having a text Pro2,
    Cell Q4 having a text Pro3,
    Cell R1 having a text LTR,
    Cell R2 having a number 1000,
    Cell R3 having a number 1500,
    Cell R4 having a number 500.
    Cell S1 having a text Product,
    3) what excel formula to be used in cell S2 to search the column and rows of cells A1 to D4 and match the value of cell Q2 and R2 and get the corresponding value from column A
    4) what excel formula to be used in cell S3 to search the column and rows of cells A1 to D4 and match the value of cell Q3 and R3 and get the corresponding value from column A
    5) what excel formula to be used in cell S4 to search the column and rows of cells A1 to D4 and match the value of cell Q4 and R4 and get the corresponding value from column A
    Please see the attachment 1
    Thanks,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-29-2024, 02:24 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    47,997

    Re: Need Help with Match and index function please.

    Try this, copied down:
    Formula: copy to clipboard 
    =IFERROR(INDEX($A$2:$A$4,MATCH($R2,INDEX($B$2:$D$4,0,MATCH($Q2,$B$1:$E$1,0)),0)),"not found")
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 09-29-2024, 02:55 PM #3
    km117
    km117 is offline
    Registered User
    Join Date
    08-31-2013
    Location
    brampton, canada
    MS-Off Ver
    Excel 2019
    Posts
    41

    SOLVED: Need Help with Match and index function please.

    Thank you so much, it worked.
    Last edited by km117; 09-29-2024 at 03:53 PM.
    Reply With QuoteRegister To Reply
  4. 09-30-2024, 07:14 AM #4
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    47,997

    Re: SOLVED: Need Help with Match and index function please.

    Quote Originally Posted by km117 View Post
    Thank you so much, it worked.

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
  5. 09-29-2024, 05:48 PM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: Need Help with Match and index function please.

    km117,
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Dave
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] index match function vs index match vba type mismatch
    By johnstylez in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-14-2022, 03:29 PM
  2. VBA function to match multiple criteria faster than vlookup or index match
    By bkav1991 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-09-2020, 09:14 AM
  3. Using index match function instead of VLOOKUP to match two criteria in another worksheet
    By PHHack in forum Excel General
    Replies: 7
    Last Post: 10-03-2019, 11:23 AM
  4. How to apply an Index.Match.Match function to all entries in a Listbox on a User Form
    By jason.drozd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-25-2018, 01:54 AM
  5. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  6. [SOLVED] Stuck on Match function with #N/A; attempting to reverse Index/Match
    By Cappytano in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-10-2014, 06:39 PM
  7. Lookup or Index(match) function not in ascending order and not an exact match
    By Tobias1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 12:37 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1