Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    A lookup to take into account 2 column values and 1 header value

    Hi,

    Is there a formular that can lookup 2 rows and 1 heading and return a value, i.e

    A B C D E F G
    1 102118 103118 104118 105118 106118
    2 6111 EU1 10,000.00 8,000.00 6,000.00 4,000.00 2,000.00
    3 6111 GB1 9,500.00 7,500.00 5,500.00 3,500.00 1,500.00
    4 6111 US1 9,000.00 7,000.00 5,000.00 3,000.00 1,000.00
    5 7111 GB1 20,000.00 18,000.00 16,000.00 14,000.00 12,000.00

    102118 6111 GB1 9,500.00
    103118 6111 US1 7,000.00

    I would like to check the combination of columns A & B combinded with the value of row A which only exist in Row 1 102118 6111 GB1 in the above table and return the value of 9,500

    Hope this makes sense.

    Thansk

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    It doesn't quite make sense (just for instance what is "row A" ?). I can imagine what you might mean but I think we need to be a little more precise than that !

    Please could you have another go at explaining exactly what you are trying to so ?

    Thanks

    PS It might be clearer if you can post a small example Workbook, showing the data you have and indicating what result you are looking for.

  3. #3
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,

    Sure thing, thanks for the quick response.

    I canít seem to upload documents so I will try and explain.

    I am doing a Vlookup checking 1 value in a table and returning the result.

    I need to check column A combined with the value of column B and then checking this to the row headers to find the third match.

    102118 103118
    6666 EU1 AAA BBB
    6666 GB1 CCC DDD
    7777 US1 EEE FFF
    7777 GB1 GGG HHH



    I want to check 6666 which is in column A and EU1 which is in column B and then the third argument is in row 1 which is 102118 which should give me the response AAA.

    Thanks

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    See if the attached file gives you what you need.
    You need to combine stuff for the lookup.

    zeddy
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi stimpsond1

    INDEX & MATCH should achieve the result you are after. See the attached file. The formula in D8 is an array formula and entered as CTRL + SHIFT + ENTER not just enter, then copy down.

  6. The Following User Says Thank You to Kevin@Radstock For This Useful Post:

    stimpsond1 (2013-02-13)

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Kevin

    Nice solution to combine the lookup values using an array formula.

    zeddy

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Kevin

    Nice solution to combine the lookup values using an array formula.

    zeddy

  9. #8
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi zeddy

    Thank you.

  10. #9
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Kevin,

    That is very impressive, thanks so much for you help.

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just as an alternative:
    =LOOKUP(2,1/(($A$2:$A$5=A8)*($B$2:$B$5=B8)),INDEX($C$2:$G$5,0, MATCH(C8,$C$1:$G$1,0)))
    which doesn't require array-entering.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi rory

    Nice alternative.
    So which is more calc efficient, arrays or non-arrays?
    Would there be any difference in file sizes say, for 10,000 source records?

    zeddy

  13. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    File size will only depend on formula length so there wouldn't be much in it. In terms of processing I would expect the non-array version to be slightly faster but again, there probably wouldn't be a lot in it. Using a key column in the lookup table would be preferable if possible.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #13
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi

    Personally I think most people on the forums don't have really huge files so speed, time etc is not a big problem, but I could be wrong.

    @ zeddy. See the attached link with regards to calculation times, there is also a vba timer. May be of interest or not!

    Here is another non array that works for the above, for excel 2010>
    =INDEX($C$2:$G$5,AGGREGATE(15,6,ROW($1:$6)/(($A$2:$A$5=$A9)*($B$2:$B$5=$B9)),1),MATCH($C9,$C$ 1:$G$1,0))

    http://msdn.microsoft.com/en-us/library/aa730921.aspx
    Last edited by Kevin@Radstock; 2013-02-13 at 10:57.

Posting Permissions

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