title: Referencing a table to look up values between worksheets in Excel
body: <p>Ok so here's a doosy of a question:</p>
  
  <p>I work in healthcare, currently I have a workbook that references values using an INDEX:MATCH function based off of a patients name, which I manually enter.  I pull 7/10 columns using this method automatically.  The 8th column is the generation date of the information and the 9th column is a date/time stamp from another sheet which I enter manually based off of the patient's room number.</p>
  
  <p>Here's the tricky part.  The two reports, which I'm pulling from our bed management program Allscripts, don't translate the bed names/numbers the same way.  So I created a translation table in a different sheet.  My question is this:  Is there a way I can use a function like INDEX:MATCH, VLOOKUP or something else within either Excel or VBA to reference my translation table in order to look up the bed name/number and auto fill the information I need?</p>
  
  <p>Spreadsheet looks like this:</p>
  
  <p><a href="https://docs.google.com/spreadsheets/d/1j8b2jZ7ZUzKpmoqoisFBVccDKw_CWDfWCGL_CcO2hCo/edit?usp=sharing" rel="nofollow">https://docs.google.com/spreadsheets/d/1j8b2jZ7ZUzKpmoqoisFBVccDKw_CWDfWCGL_CcO2hCo/edit?usp=sharing</a></p>
  
  <p>So Column G on the 'Raw Data Page' needs to reference column E from the same page, translate that bed name based off of the 'Bed Translation Page' and then look up the date/time information from the 'Job Activity Detail' page in Column G for that translated bed.  The catch is the bed names are repeated over 100k times in my current workbook due to the day by day entry of data (I manually change the search range in the INDEX:MATCH function for each column).</p>
  
  <p>I hope someone can shed some light on this for me, it's been the bane of my existence for nearly two years now!  Appreciate any info/help any of you may be able to provide!  :)</p>