Complicated Lookup in Excel

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

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Complicated Lookup in Excel », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).