Complicated Lookup in Excel [Solved/Closed]

Report
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010
-
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010
-
For me at least.....

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!!!!

Here is a mock up of data I put together to show you visually what I am trying to achieve.


https://authentification.site/files/23929859/PTCI_TECI_Example.xls



5 replies

Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010

I can see where you are going, but if you look at the Columns on the TECI Sheet, the fields aren't matching up with the "Key Fields" column.

This is pretty much taking a row from one sheet and transposing it to another.

I really appreciate all your efforts. I'm good at excel, just not THIS good.
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
Tell Me In Which Cells You Want The Formula.........

Tell Me The Direct Patch Of Those Cells....

& Tell Me From Which Cell You Want To Lookup Your Data...
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010

I will be looking up the concatenated cells as the vlookup. Once the vlookup takes place, I need the Rows on the TECI rows to be transposed to the PTCI Column according to the POTS for Key Field Blank through 6.

Does that make sense?
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010

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