Forum Discussion
Solved
Need help with a lookup
I am trying to figure out if there is a way to do a lookup that is a bit different than the normal lookups. The first image below shows initials on the top and dates on the left. I am trying to have this lookup return values for only the dates that have data in it from the second sheet.
Sheet 1:
I am going to use initials AHR for my example. In sheet 1 we see that there is no data in any of the cells excluding the dates/initials. In Sheet 2 you will notice the initials in Column B, dates in Column F, and the shift numbers I am looking to put in sheet one in column L.
Is there a way for me to do a lookup that will look at the dates in Column C and the the Initials in row 19 and return the corresponding shift numbers from Sheet 2?
The end result would look like this:
Thanks for taking your time to respond!
=IFERROR((INDEX('Sheet 2'!$L$3:$L$21,MATCH(1,('Sheet 1'!$C39='Sheet 2'!$F$3:$F$21)*(E$19='Sheet 2'!$B$3:$B$21),0))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell E39 in sheet 1 and filled across range E39:G54.
Sheet 1
Sheet 2
=IFERROR((INDEX('Sheet 2'!$L$3:$L$21,MATCH(1,('Sheet 1'!$C39='Sheet 2'!$F$3:$F$21)*(E$19='Sheet 2'!$B$3:$B$21),0))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell E39 in sheet 1 and filled across range E39:G54.
Sheet 1
Sheet 2
- Thank you so much! This was way above my skill level. Any advice on how to hone my skills with this? Thanks again!!
You are welcome! Here is information on INDEX and MATCH. This page also has information on many other Excel functions.
Two-way lookup with INDEX and MATCH - Excel formula | Exceljet
- Thank you for the reply!
