• 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...

  • 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

Shan Li

New Member
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
 
@Shan Li
First upload the workbook instead of image for better help from members.

Attached work book with formula for you.

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
 

Attachments

  • match.xlsx
    11.7 KB · Views: 5
@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:
Ok, let's try again.

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

Thanks!
 

Attachments

  • match.xlsx
    10.4 KB · Views: 6
Please see attachment

Regards
Bosco

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
 
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
Siga,

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

Regards
Bosco
 
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
 
Back
Top