Share via

Can anyone help me find the excel formula for below?

Anonymous
2024-03-16T11:31:43+00:00

Hi there,

Can anyone help me find the excel formula for following:

  • Sheet1 has IDs in column A, Test dates in column B, and corresponding scores in column C.
  • Sheet 2 has larger range of IDs in column A, some rows in column B have test dates.

The formula needs to find the matching ID from column A of sheet1, then look up Test date beside it in column B for that row, and when finds a matching date as well as ID, needs to return the value of column c for that row into sheet2 column C.

I have tried these with no luck as first 2 returned 'No Match' even though there definitely are matches in Sheet1, and last returned N/A.

=IFERROR(INDEX(Sheet1!$C$3:$C$583,MATCH(A3, Sheet1!$A$3:$A$583,0)+MATCH($B$3, Sheet1!$B$3:$B$583,0)-1),"No Match")

=IFERROR(INDEX(Sheet1!$C$3:$C$583,MATCH(A3&$B$3,Sheet1!$A$3:$A$583&Sheet1!$B$3:$B$583,0)),"No Match") Note: can't concatenate fields in A and B

=INDEX(Sheet1!$C$3:$C$720, MATCH($A3&$B3,Sheet1!$A$3:$A$720&Sheet1!$B$3:$B$720, 0))

Have also tried some multiple condition VLOOKUP formulae and XLOOKUP formulae, with no success there either!

Appreciate any help!

Kind regards,

AC

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-03-16T12:39:48+00:00

    Perhaps

    =IFERROR(INDEX(Sheet1!$C$3:$C$583, MATCH(1, (Sheet1!$A$3:$A$583=A3)*(INT(Sheet1!$B$3:$B$583)=INT(B3)), 0)),"No Match")

    or

    =FILTER(Sheet1!$C$3:$C$583, (Sheet1!$A$3:$A$583=A3)*(INT(Sheet1!$B$3:$B$583)=INT(B3)), "No Match")

    1 person found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-03-16T12:33:55+00:00

    Okay, I think you're onto something, HansV. The dates for both sheets are displaying as dd/mm/yyyy (i.e. short date format selected); however, in sheet1 when I click on the dated cells, the date shows the date and also a time up in the fx line (from an exported report).

    I don't know how to convert this to retain only dd/mm/yyyy. Any ideas? Copy and Paste123 doesn't work, and changing format only changes what is displayed.

    Regards and thanks again,

    Adele.

    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-03-16T12:06:03+00:00

    Check carefully whether there are any EXACT matches - if one sheet has "Jones" and the other has "Jones " with a space at the end, Excel does not recognize that as a match.

    The same holds if one sheet has 02/05/2024 and the other has 02/05/2024 08:15

    0 comments
  3. Anonymous
    2024-03-16T11:58:45+00:00

    Hi @HansV,

    Thanks for your response and suggestions.

    I've tried both but, unfortunately, they are also returning No Match for every row, including those that definitely have ID and Test Date matches in Sheet1.

    Am I missing something else?

    Thanks

    Adele

    0 comments
  4. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-03-16T11:41:49+00:00

    Try

    =IFERROR(INDEX(Sheet1!$C$3:$C$583, MATCH(1, (Sheet1!$A$3:$A$583=A3)*(Sheet1!$B$3:$B$583=B3), 0)),"No Match")

    or

    =FILTER(Sheet1!$C$3:$C$583, (Sheet1!$A$3:$A$583=A3)*(Sheet1!$B$3:$B$583=B3), "No Match")

    0 comments