I have two tables in two different sheets that I need to use to combine info.

Sheet 1 - Table 1:

    A   |    B      |    C  
12-2-19 | Monday    | Function Needed here
12-3-19 | Tuesday   | Function Needed here
12-4-19 | Wednesday | Function Needed here
12-5-19 | Thursday  | Function Needed here
12-6-19 | Friday    | Function Needed here

Sheet 2 - Table 2:

   A      |  B
Monday    | text1
Tuesday   | text2
Wednesday | text3
Thursday  | text4
Friday    | text5

Basically where it says Function Needed Here (Col C Sheet 1) I want it to pull in text1, text2, etc. (Col B Sheet 2) based on the day of the week (Col B Sheet 1)

My idea was to use a functionlike this in Col C of Sheet 1:

=IF(B1='Sheet 2'!A1,'Sheet 2'!B1,"--")

But that only checks for Monday (and works) but I want to have it check all the days and return the corresponding text.

How can I have it check the entire range of Col A Sheet 2 for the value in Col B Sheet 1 and return the corresponding value in Col B Sheet 2 to Col C Sheet 1.

NOTE: Sheet 1 is going to be a calendar and have all the dates/days of the week for the entire month of December. There will also be corresponding sheets for January, February, March, etc. so the formula needs to always default to the static range in Sheet 2 Cols A&B Rows 1-5. Only the rows in Col B and C in Sheet 1 will change dynamically (B1, B2, etc.) I will change the sheet names if needed.

Thanks in advance for your help!

  • 1
    This is very doable with either INDEX/MATCH or VLOOKUP but the exact method will depend on whether your days-of-the-week are actual dates in a cell formatted as dddd or text either derived from a date like =TEXT(A2, "dddd") or simple typed in as Monday, Tuesday, etc. In either case, the two tables should use the same method for showing the day-of-the-week. – user385793 Nov 26 '19 at 1:54
  • It is using =TEXT(A2, "dddd") in Sheet 1 and in Sheet 2 it is just text (Monday, Tuesday, etc.) – Matt Minten Nov 26 '19 at 1:57
  • Then either =VLOOKUP(B2, Sheet2!A:B, 2, FALSE) or =INDEX(Sheet2!B:B, MATCH(B2, Sheet2!A:A, 0)) should do nicely. – user385793 Nov 26 '19 at 2:01
  • The VLOOKUP worked great! I have been out of excel for a few years and forgot about that. Thanks! – Matt Minten Nov 27 '19 at 16:43

Providing that the days-of-the-week from Sheet1!A:A and Sheet2!B:B are the same type of data, a simple INDEX/MATCH or VLOOKUP should be sufficient.

=VLOOKUP(B2, Sheet2!A:B, 2, FALSE)
=INDEX(Sheet2!B:B, MATCH(B2, Sheet2!A:A, 0))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.