Index/Match search in 3 seperate workbook

[RMC1218]

Hi,
Some assistance please!
I have been working on this 2 weeks and have finally come to the conclusion I need HELP!
I am trying to use index/match to find an article number in any 1 of 3 attribute files workbooks. I have been am using Vlookup, but the Master attribute file workbooks DO NOT list the article number in column A (but list them in F) and therefore I have been creating an altered copy every month to place the article numbers in column A so VLOOKUP would work. Index/Match would be a more efficient look up formula as I could link directly to the Master Attribute files without altering the workbooks.
Below is the formula I have been using that looks in 2 attribute files workbooks
=IF(ISNA(VLOOKUP(A8,'C:\Recon Workbook Files\Service Files\AttributeFiles\[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)),IF(ISNA(VLOOKUP(A8,'C:\Recon Workbook Files\Service Files\AttributeFiles\[PartAttributes.xls]PartAttributes'!A:D,2,FALSE)),"",(VLOOKUP(A8,'C:\Recon Workbook Files\Service Files\AttributeFiles\[PartAttributes.xls]PartAttributes'!A:D,2,FALSE))),(VLOOKUP(A8,'C:\Recon Workbook Files\Service Files\AttributeFiles\[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)))

Below are the three (the new formula would incorporate a 3rd attributes workbook "Other" to the search criteria) Index/Match formulas I would like to "Tie" together to find the article number in any one of the 3 attribute files workbooks and return column A if the article number is present or nothing if the article number is not present in any of the 3.
=INDEX('C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!A:A,MATCH(A8,'C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!F:F,0))
=INDEX('C:\JDAAttributeFiles\[PartAttributes.xls]PartAttributes'!A:A,MATCH(A8,'C:\JDAAttributeFiles\[PartAttributes.xls]PartAttributes'!F:F,0))
=INDEX('C:\JDAAttributeFiles\[TireAttributes.xls]TireAttributes'!A:A,MATCH(A8,'C:\JDAAttributeFiles\[TireAttributes.xls]TireAttributes'!F:F,0))

Thank You very much in advance
RMC

 

[Sal Paradise]

Personally I would make three names:
wbkOther: 'C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!F:F
wbkPart: 'C:\JDAAttributeFiles\[PartAttributes.xls]PartAttributes'!F:F
wbkTire: 'C:\JDAAttributeFiles\[TireAttributes.xls]TireAttributes'!F:F

Assuming the article numbers are numbers, and there are no duplicates (there won't be a match in 2 different sheets):
=CHOOSE(NOT(ISNA(MATCH(A8,wbkOther,0)))*1+NOT(ISNA(MATCH(A8,wbkPart,0)))*2+NOT(ISNA(MATCH(A8,wbkTire,0)))*3,INDEX(OFFSET(wbkOther,0,-5),MATCH(A8,wbkOther,0)),INDEX(OFFSET(wbkPart,0,-5),MATCH(A8,wbkPart,0)),INDEX(OFFSET(wbkTire,0,-5),MATCH(A8,wbkTire,0)))

Basically, see which sheet has the value, then index the appropriate sheet.

 

[Aladin Akyurek]

If a text result is expected...

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4},"",
VLOOKUP(A8,'C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!A:F,5,0),
VLOOKUP(A8,'C:\JDAAttributeFiles\[PartAttributes.xls]PartAttributes'!A:F,5,0),
VLOOKUP(A8,'C:\JDAAttributeFiles\[TireAttributes.xls]TireAttributes'!A:F,5,0))

If a numeric result is expected...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4},0,
 VLOOKUP(A8,'C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!A:F,5,0),
 VLOOKUP(A8,'C:\JDAAttributeFiles\[PartAttributes.xls]PartAttributes'!A:F,5,0),
 VLOOKUP(A8,'C:\JDAAttributeFiles\[TireAttributes.xls]TireAttributes'!A:F,5,0))

 

[Sal Paradise]

I scoured the net for how to do that Aladdin, but clearly I didn't put enough elbow grease into it. Mental note to remember how to use bignum...

 

[RMC1218]

Gentlemen,

Thank You for your responses. I apologize for the delay in my responding.

Sal,
Your response is very interesting and appears to be what I am looking for, however as much as I have worked with it I cannot get a response from the workbooks. The message I receive when pasting the formula "The Formula you typed contains an error". I have studied it and do not see the issue. The fault may lie in the names as suggested. I am familiar with range names and have named the three worksheets within the workbooks as suggested, but I suspect a different approach is required to create the link to the workbooks. I have replaced the names in the formula's with the addresses with no success either.

Aladin,
Thank you for the formula supplied, however although there is not an error message when pasting it in, there is nothing returned. Being the formula is a VLookup wouldn't the searched data be required in column A? I have tried it with the data in column A and Column F as the Master attribute files are in column F as explained.

Gentlemen further assistance would be greatly appreciated.
Thank You
RMC

 

[Aladin Akyurek]

...
Aladin,
Thank you for the formula supplied, however although there is not an error message when pasting it in, there is nothing returned. Being the formula is a VLookup wouldn't the searched data be required in column A? I have tried it with the data in column A and Column F as the Master attribute files are in column F as explained.

Gentlemen further assistance would be greatly appreciated.
Thank You
RMC

What would be the formula if you just looked up a single table? And what kind of result are you expecting - text or number?

 

[RMC1218]

The formula for the other attribute workbook/file would be
=INDEX('C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!A:A,MATCH(A8,'C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!F:F,0))

The response would be text only - in all workbooks

 

[Aladin Akyurek]

The formula for the other attribute workbook/file would be
=INDEX('C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!A:A,MATCH(A8,'C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'!F:F,0))

The response would be text only - in all workbooks

X = 'C:\JDAAttributeFiles\[OtherAttributes.xls]OtherAttributes'
Y = 'C:\JDAAttributeFiles\[PartAttributes.xls]PartAttributes'
Z ='C:\JDAAttributeFiles\[TireAttributes.xls]TireAttributes'

Try...

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4},"",
  INDEX(X!A:A,MATCH(A8,X!F:F,0)),
  INDEX(Y!A:A,MATCH(A8,Y!F:F,0)),
  INDEX(Z!A:A,MATCH(A8,Z!F:F,0))))

Substitute the longer names including the quotes for X, Y, and Z in the foregoing formula.

 

[RMC1218]

WOW!!!

It is working perfectly.
If it's important I cannot remove the [X], [Y], [Z]. When the links are updated it inserts the correct file name but leaves those in. If I try to remove them the PartsAttribute workbook will not return answers (the "Tire" and "other" will still work).

Please let me know if you think that may be an issue. I will try this at work tomorrow.

Thank You Very Much
RMC

 

[Aladin Akyurek]

WOW!!!

It is working perfectly.[/code]

Great. Thanks for providing feedback.

If it's important I cannot remove the [X], [Y], [Z]. When the links are updated it inserts the correct file name but leaves those in. If I try to remove them the PartsAttribute workbook will not return answers (the "Tire" and "other" will still work).

Please let me know if you think that may be an issue. I will try this at work tomorrow...

The file paths must be in the formula. I used X, Y, Z as placeholder in order to ease posting...