1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help with value look up and match from two tables

Discussion in 'Ask an Excel Question' started by Shan Li, Apr 20, 2017.

  1. Shan Li

    Shan Li New Member

    Messages:
    4
    Hi
    I'm new to this forum (the only forum I have ever joined) and i hope to learn a few things to improve my excel skills.

    I'd like to get help with a formula that does this please


    excel.jpg

    Also, if you are able to provide the VB codes for this that'd be even more awesome.

    Many thanks in advance!

    ShanLi
  2. ashokkumarkolla

    ashokkumarkolla Member

    Messages:
    57
    @Shan Li
    First upload the workbook instead of image for better help from members.

    Attached work book with formula for you.

    Attached Files:

  3. Shan Li

    Shan Li New Member

    Messages:
    4
    Hi Ashokkumarkolla

    Many thanks for your quick reply and the tip to upload the file instead of the image.

    The formula would only work if it is a one-on-one match however if I change the order of the data in the table on the right hand side, it would not work. It will have to be something like an index-match thing but i can't work it out .

    Here i post my original problem again with a revised table.


    Hi
    I'm new to this forum (the only forum I have ever joined) and i hope to learn a few things to improve my excel skills.

    I'd like to get help with a formula that does this please

    Also, if you are able to provide the VB codes for this that'd be even more awesome.

    Many thanks in advance!

    ShanLi

    Attached Files:

  4. ashokkumarkolla

    ashokkumarkolla Member

    Messages:
    57
    @Shan Li
    ok, now you want to lookup the the data range.

    Your data is having same values in column"A" with different values in corresponding columns.

    what EXACTLY are you wanted to do ???

    what "return value" to be placed in Column J?

    Ask the question in proper section if you need VBA solution.
    Last edited: Apr 20, 2017
  5. Shan Li

    Shan Li New Member

    Messages:
    4
    Ok, let's try again.

    I have simplified the table and see if this makes more sense.

    Thanks!

    Attached Files:

  6. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    884
    Please see attachment

    Regards
    Bosco

    Attached Files:

    Shan Li, Siga and Thomas Kuriakose like this.
  7. Siga

    Siga Member

    Messages:
    35
    Bosco,

    In Shan Li example the first row in the first table (Date 2017.05.07 in A2:E2) matches 2 values from Table 2. It could be row G3:K3 rate 2.90 and G9:K9 rate 2.50).

    Your formula catches the first value? or the highest value?

    And actually it is not clear what Shan Li needs in this example, what if there are more than 1 possible value?

    Siga
    Shan Li likes this.
  8. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    884
    Siga,

    I note that already, my formula is based on the 1st value found, that is the lowest value.

    Regards
    Bosco
    Shan Li and Siga like this.
  9. Shan Li

    Shan Li New Member

    Messages:
    4
    Hi Bosco and Siga

    many thanks for both of your help. Bosco's formula works perfectly for what I asked for however I realise now what was missing in my question.

    Take the first line for example, the rate returned is 2.9. The answer I was looking for is actually 2.5. This is because i wanted the closest match between Mdate and Edate where Mdate also has to be less than the Edate. i.e. 15/6/2017 is closest to 28/6/2017.

    In my very first spreadsheet I included the calculated number of days between Ddate and mdate, Sdate and Edate, i wonder if that would help with determining the closest match?

    Many thanks!
    Shanli

Share This Page