+ Reply to Thread
Results 1 to 3 of 3

How to use lookup/match/index in a table using variables to find the correct value

Hybrid View

hinta How to use lookup/match/index... 04-28-2013, 04:42 PM
oeldere Re: How to use... 04-28-2013, 04:55 PM
Tony Valko Re: How to use... 04-28-2013, 05:55 PM
  1. #1
    Registered User
    Join Date
    04-28-2013
    Location
    Salt Lake City, Utah, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question How to use lookup/match/index in a table using variables to find the correct value

    I have a table of values that looks like this:
    aaa 2017 22.1 2028 36.2
    zzz 2026 10.0 2032 24.0
    kkk 2013 14.1 2015 13.5

    The table is not sorted in any way.

    I would like to search down the 1st column to find a value matching the contents of another cell, say A1, then move along that row to find the column matching the contents of another cell, say A2.

    Let's say A1 = kkk, A2 = 2015. Then I want to get the value for the cell to the right, in this case = 13.5. I have tried all kinds of functions, like lookup, Index, Match etc with no luck. I can find the correct row, but I don't know how to look in that row to find the right column. Can anyone help? Thanks.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to use lookup/match/index in a table using variables to find the correct value

    Are able to change the format of the data in column D and E to column B and C.

    In that case you can use index / matsch to find your data.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to use lookup/match/index in a table using variables to find the correct value

    Maybe this...

    Data in the range A10:E12.

    A1 = KKK
    A2 = 2015

    =SUMPRODUCT(--(A10:A15=A1),((B10:B15=A2)*C10:C15)+((D10:D15=A2)*E10:E15))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1