index match

[blicop]

Please help. Cant get index and match to work

<tbody>
</tbody>

Location	Old Code	New Code
A	AVR	=INDEX(Table2,MATCH([@Old Code],Table2[#All],0),8)
N	VVB
G	XX
W	YY
p	9S
C	RCM1M
T	H

<tbody>
</tbody>

Table 2:

A	N	G	W	P	C	T	New Code
ABB	PPS			2PL			AB
APB							AP
	SSW						SS
				9PL			AL
APG							GG
AVG	YVG			2BF			GA
AVH	YVZ			9BF			GR
AVN	FVG						AA
AVR	VVB			9S			VR
	VVZ						SB
BBB							YA
BBF							BG
BDL							LS
BFB							FA
BFV							FV
BPG					RCM1M		PG
BPT							CP
BSG							SG
BSS							SW
BVD							VG
BVR							HG
BVA							VA
BVS							VS
BVL							CD
BWC							WT
BWD							WT
BWW							WT
BZB							VZ
BZG							VZ
BLT							FT

<tbody>
</tbody>

 

[steve the fish]

Here's an index match match or another option using lookup:

=INDEX(Table2[[#All],[New Code]],MATCH(Table1[@[Old Code]],INDEX(Table2[#All],,MATCH(Table1[@Location],Table2[#Headers],0)),0))
=LOOKUP(2,1/(INDEX(Table2[#All],,MATCH(Table1[@Location],Table2[#Headers],0))=Table1[@[Old Code]]),Table2[[#All],[New Code]])

 

[DRSteele]

This will also suffice in C3:

=IFNA(INDEX(Table2,MATCH([Old Code],INDEX(Table2,,MATCH([@Location],Table2[[#Headers],[A]:[T]],0)),0),8),"")

 

[blicop]

im getting issues with both. heres the sample file for download so its easier to check

https://file.town/download/wopf83jujzf3u45rk0vqntkd0

 

[steve the fish]

It works based on your example. Just change the table names to suit.

 

[blicop]

got it. had my table names mixed up