using multiple sheets within the same file for vlookup

[MsWendy]

Hi

Im trying to combine my table array in vlookup to include to sheets within the same file for my vlookup but its not accepting it. Below are the two vlookups that I am using, how can i combine them to search in both?

=VLOOKUP(F5,'P:\Bata\Bond\N20\[Nature 20''s Catalogue DHL.xls]2012'!C:F,4,0)
=VLOOKUP(F6,'[Nature 20''s Catalogue DHL from 2012- .xls]2013'!$C:$I,4,0)

[bentleybob]

How would the results look if either or both lookups work? Are you trying to add numbers from two locations, or perhaps merge text? It makes a difference as to what you're expecting. For example, you can simply combine the two formulas with an ampersand ("&") if you want text with no separator, or combine them with a "+" if you expect numbers. You might have issues if one results in #NA, so you may have to do some error trapping first. If you can be more specific -- or upload a small sample file -- it shouldn't be hard to do what you want.

[MsWendy]

the text that I am after will either be found in one or the sheet, the data will not be contained in both, make sense? I want it to copy exactly what is found in colum 4 of either files depending on which one it is entered in.

[MsWendy]

sorry, the data in colum 4 is either enter in 2012 or 2013 not both

[bentleybob]

This will work if the data you're trying to look up is text:

Please Login or Register  to view this content.

If your looking for a value, substitute a zero for the double quotes and a plus sign for the ampersand.

Does this work for you?

[MsWendy]

no, its saying #NAME?

[bentleybob]

Do both of your formulas work by themselves? All I've done is to combine them. The two do look to different files; perhaps there's a typo somewhere?

If not, try these two pieces separately to see if you can't locate the error:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I do see in the second one there's a space after 2012- and before .xls; was that intentional?

[MsWendy]

I have checked both and both are correct?

[bentleybob]

Sorry, my bad. Try:

Please Login or Register  to view this content.