Results 1 to 6 of 6

Thread: INDEX MATCH Query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2016
    Location
    Birmingham UK
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default INDEX MATCH Query

    Hi

    I think this may be an Index/Match formula but not 100%

    Trying to work out a cost based on two factors as displayed below.

    Hardware Category Berlin Amsterdam London Frankfurt Copenhagen Lisbon Madrid Barcelona
    1 175 175 35 175 135 235 235 235
    2 185 185 35 15 255 255 255 255

    Then I have a table to with drop downs to select which Hardware Category and which City for delivery:

    Destination Hardware Category Cost

    I am having trouble populating the Cost cell (C2)

    Thanks in advance

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,384
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: INDEX MATCH Query

    Quote Originally Posted by tcarwardine View Post
    Hi

    I think this may be an Index/Match formula but not 100%
    You are correct.

    INDEX MATCH

    ABCDEFGHI
    1Hardware CategoryBerlinAmsterdamLondonFrankfurtCopenhagenLisbonMadridBarcelona
    2117517535175135235235235
    321851853515255255255255
    4
    5
    6DestinationHardware CategoryCost
    7Frankfurt215

    Spreadsheet Formulas
    CellFormula
    C7=INDEX($B$2:$I$3,MATCH(B7,$A$2:$A$3,0),MATCH(A7,$B$1:$I$1,0))


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Aug 19th, 2019 at 09:09 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,259
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: INDEX MATCH Query

    Adjust the formula as necessary

    Sheet1!A1 is "Hardware Category"
    Sheet2!A1 is "Destination"

    in Sheet2!C2
    =INDEX(Sheet1!B2:I3,MATCH(A2,Sheet1!B$1:I$1,0),MATCH(B2,Sheet1!A$2:A$3,0))

  4. #4
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,115
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH Query

    Maybe something like this.

    Lets assume your first table is in the range A4:I6.

    In C2
    Code:
    =OFFSET($A$4,MATCH($B$2,$A$5:$A$6,0),MATCH($A$2,$B$4:$I$4,0),1,1)
    There are other ways of doing this as well.
    Last edited by Gerald Higgins; Aug 19th, 2019 at 09:12 AM.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  5. #5
    New Member
    Join Date
    Jul 2016
    Location
    Birmingham UK
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH Query

    perfect. thanks very much

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,384
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: INDEX MATCH Query

    Quote Originally Posted by tcarwardine View Post
    perfect. thanks very much
    You're welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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