Simple cross reference index in excel (Network Steve Forum)

Simple cross reference index in excel

hi, I need a spread sheet that does matching/cross referencing with the below
rules:
I don't see where I can attach my xls, so it can be seen at :: http://www.aanning.com/ajissues/Excel/xrefs/

I believe I only need 4 relatively simple formulas, please advise, and
thanks - James - Janning197@aol.com

1) Columns A,B,C, E and F will be static values A,B, C 400 rows, E and F
1000+ rows
2) Columns A,B,C, E and F are free form, no formulas, pasted in
from another soure, will never change size or order
3) Columns A,B,C, E and F
are the reference, index or lookup tables 4) colunns H, M and Q will be provided. Free form, pasted in, may be in any
random order.
5) Columns A and H will must contain the same dataa, but may
not be in the same order.
6) column J will be be the same as column J, except
depending on column C will have a leading E or C (ie J4 should be C100003 and J2
should be E100005)
7) Columns A and H will must contain the same dataa, but
may not be in the same order.
8) Column I shuould be the cross refence of A/B
(ie I6 should be 100002) 9) Column M may have duplicate values (several people can have the same
manager)
10) Column N shuould be the cross refence of A/B (ie N6 and N7
should be 100005) 11) Column Q may have duplicate values (several people can have the same
default GL code)
12) Column R huould be the cross refence of E/F (ie R
should be R6 should be 31 So, 4 Rules (formulas), in columns I, J, N and R

September 5th, 2015 5:33am

> column J will be be the same as column J

That is the easy part! ;-)

In I2: =VLOOKUP(H2,$A$2:$B$400,2,FALSE)

In J2:=LEFT(VLOOKUP(H2,$A$2:$C$400,3,FALSE),1)&I2

In N2: =IFERROR(VLOOKUP(M2,$A$2:$B$400,2,FALSE),"")

Adjust the row number 400 as needed.

Select I2:J2 and fill down to the end of the data.

Select N2 and fill down to the end of the data.

In R2: =VLOOKUP(Q2,$E$2:$F$1000,2,FALSE)

Adjust the row number 1000 as needed.

Select R2 and fill down to the end of the data.

Free Windows Admin Tool Kit Click here and download it now

September 5th, 2015 7:45am

Thank you!!! works like a champ!!!!!!!!!!!

September 5th, 2015 10:25am

This topic is archived. No further replies will be accepted.