Vlookup help cross referencing cell against another sheet to return value of another cell

[Alex p h]

Can someone please help I’m trying to make a order sheet that translates alternative part numbers to my part numbers. I have a full list of the numbers in one sheet and in main sheet I’m trying to use Vlookup to find the exact match of what I type in to a cell from the other sheet and if is found then returns what is in column a in that row it has been found in, but it just won’t work for some reason. Any help would be greatly appreciated

 

[Alex p h]

ok, so remove the size column lookup part for column C

test (1).xlsx
	A	B	C
1
2
3
4		Part number for input to be cross referenced on other sheet	Part number to retrieve from colum a on other sheet if match is found
5
6	Qty	Part Number	Truflow Part Number
7		S27-0202	B1M-02-02
8		1/4-1/8FF33MS	B1M-02-04
9		A-A-0206	B1M-02-06
10			no Match
11			no Match
12			no Match
13			no Match
14			no Match
15			no Match
16			no Match
17
order

Cell Formulas
Range	Formula
C7:C16	C7	=IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!D:D,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!E:E,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!F:F,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!G:G,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!H:H,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!I:I,0)),"no Match")))))))

Thank you for your help I’ve since changed items on the reference sheet and this no longer works would you be able to have a look as to where I’ve stuffed up? I’m adding more columns on the reference sheet, do I need to add anything new to the formula?

gates conversion.xlsx

Microsoft Excel Workbook

1drv.ms

 

[etaf]

just refreshing my memory

the reason its not working is all the cells now have a ref error - so need to have the ranges added

are you looking up the part number - in column B or C or D , and when found return the TF number from column A

if so then
=IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!B:B,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!C:C,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!D:D,0)),"not found")))

we can add an IF so blanks are ignored

=IF(order!D13 = "", "" , formula
which i have added to row 26 to 30

BUT , the part you have put in i cant find

added to a share as well

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Alex p h]

just refreshing my memory

the reason its not working is all the cells now have a ref error - so need to have the ranges added

are you looking up the part number - in column B or C or D , and when found return the TF number from column A

if so then
=IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!B:B,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!C:C,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!D:D,0)),"not found")))

we can add an IF so blanks are ignored

=IF(order!D13 = "", "" , formula
which i have added to row 26 to 30

BUT , the part you have put in i cant find

added to a share as well

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thank you soo much! I realised now why that part number doesn’t work it’s because it needed a 0 before each of the 6’s so “06g-06fjx”. Is there a way I can make it return the correct result even if the zero is forgotten? Also on the worksheet order is it possible to make it change which column from fitting list worksheet the result is retuned from with a drop down selection in that column?

IMG_1865.JPG

Shared with Dropbox

www.dropbox.com

 

[etaf]

a text value with a zero in the from will be different to a number
some of your codes do have a zero and some do not
so it maybe difficult to add

for example if you have a part number
AB-1244
and also
0AB-1234
and also
0AB-01234
will they always be for the same part number ?
same for this type of code

7103-4810

also know when to add a 0 in the middle of the text like
6g-6fjx
changed to
06g-06fjx

you could in the if not found error add a NOTE

=IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!B:B,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!C:C,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!D13,'fitting list'!D:D,0)),"not found, does it need a 0?")))

 

[etaf]

BTW - using a full columm reference (over a million rows is not efficient )
so using a range like

$A$2:$A$100000
or whatever you think the maximum number of rows are likely to be, would be a lot better - so 100000 change to whatever number - its better than over million rows

=IFERROR(INDEX('fitting list'!$A$2:$A$100000 ,MATCH(order!D13,'fitting list'!$B$2:$B$100000 ,0)),IFERROR(INDEX('fitting list'!$A$2:$A$100000 ,MATCH(order!D13,'fitting list'!$C$2:$C$100000 ,0)),IFERROR(INDEX('fitting list'!$A$2:$A$100000 ,MATCH(order!D13,'fitting list'!$D$2:$D$1000000 ,0)),"not found, does it need a 0?")))

 

[Alex p h]

BTW - using a full columm reference (over a million rows is not efficient )
so using a range like

$A$2:$A$100000
or whatever you think the maximum number of rows are likely to be, would be a lot better - so 100000 change to whatever number - its better than over million rows

=IFERROR(INDEX('fitting list'!$A$2:$A$100000 ,MATCH(order!D13,'fitting list'!$B$2:$B$100000 ,0)),IFERROR(INDEX('fitting list'!$A$2:$A$100000 ,MATCH(order!D13,'fitting list'!$C$2:$C$100000 ,0)),IFERROR(INDEX('fitting list'!$A$2:$A$100000 ,MATCH(order!D13,'fitting list'!$D$2:$D$1000000 ,0)),"not found, does it need a 0?")))

Is it possible to do it to overlook any “0” but still return the full value fro the list?

 

[Alex p h]

And would it help if I made into a table then referencing the table itself wouldn’t need to reference a row range?

 

[etaf]

Is it possible to do it to overlook any “0” but still return the full value fro the list?

I'm not sure
what about
7362-0024
is that
7362-24

should that ignore 0's

G25100-0402
to
G251-42

04G-05FSX90-046
to
4G-5FSX9-46

could get very complicated with what zeros to ignore when not to ignore them, maybe possible in vba - not my area

 

[etaf]

And would it help if I made into a table then referencing the table itself wouldn’t need to reference a row range?

you could

 

[Alex p h]

I'm not sure
what about
7362-0024
is that
7362-24

should that ignore 0's

G25100-0402
to
G251-42

04G-05FSX90-046
to
4G-5FSX9-46

could get very complicated with what zeros to ignore when not to ignore them, maybe possible in vba - not my area

What I was thinking was if put 6g-6fjx it would still find the 06g-06fjx in the list