Complicated Lookup in Excel

December 2016


Complicated Lookup in Excel




Issue


I have two spreadsheets that I am trying to use a lookup on. It's a little more than a VLookup. PTCI and TECI are the names for the workbooks. In the example below, I have an example of what I am trying to do. The actual lookup part has been concatenated and then highlighted in blue. The orange is the tricky part. There are 7 columns (BLANK through Key Field 6) on the TECI that need to be translated into a number (Key) column on the PTCI. It seems to be both a Vertical Lookup and a Horizontal Lookup. I need all the help I can get ASAP. Thank you in advance!!!!

Solution

  • From the PTCI workbook, I have added the TECI workbook to 'SHEET1' on the PTCI. From there, I used this following formula to get the data that I needed.....


=VLOOKUP(H2,TECI,MATCH(G2,INDEX(TECI,1,),0),0)

H2 - Concatenated Data Field
G2 - Key Field

Note


Solved by DCecil07

Related :

This document entitled « Complicated Lookup in Excel » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.