Index, Match, and Vlookup across multiple worksheets using multiple entries

1. Index, Match, and Vlookup across multiple worksheets using multiple entries

Hey guys,

I am trying to create a worksheet that pulls information from other worksheets to create a calculation guide for products my company sells. I am having difficulty using the Index and Match functions when I go across worksheets. For example, I want to pull the price corresponding to the item (card holder) and the size of the product (3x5). Here is my current formula:

=INDEX('Two Sided Top Open'!\$D\$2:\$D\$3,MATCH('Calculation Sheet'!A3&'Calculation Sheet'!B3,'Two Sided Top Open'!\$A\$2:\$A\$3&'Two Sided Top Open'!\$B\$2:\$B\$3,0))

Even after pressing ctrl, shift, enter, I get a N/A value. Do you guys know how I can resolve this? Should I be using Vlookup instead?

I have attached the document that I am trying to fix. The problem is under the calculation worksheet. Any help would be much appreciated!

2. Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

Hi and welcome to the forum

Before I look at coming up with something for you, 1 quick question...

Why do you have seperate sheets for your data? It's almost alwats better to have all data on 1 sheet (kinda like a data base) and then reference that for other summaries/extracts etc?

3. Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

Thanks for the reply FDibbins! The reason for the multiple sheets is becuase I am testing out how to do this equation before doing in on all our other products which are on 50 different sheets. Is there any other way to compute it, without combining it all on one sheet?

4. Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

I have been working on it, and if you can somehow have your sheet names included in your product name, you can use this regular non-array formula....
=INDEX(INDIRECT("'"&LEFT(A2,SEARCH(" ",A2,1)-1)&"'!\$A:\$D"),MATCH(\$A2,INDIRECT("'"&LEFT(A2,SEARCH(" ",A2,1)-1)&"'!\$A:\$a"),0),MATCH(B\$1,\$A\$1:\$E\$1,0))

I changed your sheet name from Three sided to 3-sided

5. Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

duplicate post

6. Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

Wow thanks! I will definatelu give this a try, and I think I am going to put them all on the same worksheet, simply because it would be less messy. Thanks for all your help!

7. Re: Index, Match, and Vlookup across multiple worksheets using multiple entries

All data on 1 sheet is a great step in the right direction

let me know how you make out please?

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1