# using an Array formula to do VLOOKUP in multiple sheets

I have used below formula in this file but I do not know why it doesn not work and return #N/A!? Infact I need to lookup "Station .No" from other sheets to display it in Sheet1 :

````= VLOOKUP(I3,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!\$A\$5:\$N\$500"),I3)>0),0))&"'!\$A\$5:\$N\$500"),14,FALSE) `
```

Replay

Category: microsoft excel Time: 2016-07-28 Views: 0

## Related post

• Return value in column A in preceding cells in column B using an Array Formula 2014-09-22

I've been looking for a solution to this for days now. How do you fill cells in column B (using an array formula) that are preceding the cell in column A with the value of column A only when cell in column C is not blank? In other words, after column

• Using an array formula to add up several cells in another worksheet (using INDIRECT) 2013-04-23

I have an Excel 2007 workbook. On one worksheet, I want to add up several regularly spaced values on another worksheet. I can easily do this with a very long sum formula without an array formula but it would be much simpler/nicer/easier to see what's

• Apply VLOOKUP to multiple sheets and ignore blanks 2014-07-09

I would like to do a VLOOKUP on multiple sheets (for which I am able to define a strict priority order) but where I want to ignore the answer when it is a blank. I have found this answer: Apply VLOOKUP formula to multiple sheets The problem in this a

• Use of array formulas within VBA 2011-09-05

Excel array functions in worksheets are useful. E.g. for calculating the average of the sin values of an array A1:A5 I enter the following which works nicely: {=AVERAGE(SIN(A1:A5))} Now I would like to create a function in VBA that does the job, some

• Array formulas and Logic, checking against multiple criteria 2015-05-29

I'm currently just troubleshooting a report spreadsheet I've written. The logic I had written isn't working, so I thought I'd see if I could ask for some help here. Essentially, I have a list of contracts, a list of products, a list of end and start

• sum of vlookup using array formula 2013-01-30

I have such table of payments: account | amount --------+------- 101 | 3 101 | 5 102 | 7 103 | 9 I named the range of this table "payments". Its first column I called "accounts". Its second column I called "amounts". I also h

• Calculate weekly sum in spreadsheet using array formula 2016-02-17

I'm trying to calculate weekly sums in a single column, but I'm having trouble writing the formula. I'm found the the weeknum for every date in the year, but I can't find a way to sum up values if they have the same weeknum. Link to my spreadsheet: h

• When should SUM() be used as an array formula vs. not in Excel 2016-06-16

How come {=SUM(A2:A10*B2:B10)} works, but without CTRL+SHIFT+ENTER it doesn't? I'm trying to learn more about array formulas, but I wish there was some guide about what goes on behind-the-scenes. Doesn't A2:A10*B2:B10 simply spit out an array of mult

• Excel - Alternatives to array formula for multiple criteria lookup? 2016-02-22

I'm comfortable with array formulas, but my co-workers are rebelling against them. Is there an easy way that I'm overlooking to accomplish the following? I've got data in the following format: | A | B | C | ..... | N | | ZIP | City | County | ..... |

• Find Array Intersection of 2 Match Array Formula 2013-03-10

How do I find intersection of two match function by using array formula (pressing CTR+SHIFT+ENTER keys). Currently I try using this array formula but cannot get any result. {=MATCH(B2,Sheet2!\$A\$2:\$A\$5) MATCH(C2,Sheet2!\$B\$2:\$B\$5)} For example, data in

• Can you define an array formula across multiple columns? 2014-02-04

I have a table of Stores (well, for this example anyway): Store_ID Store_Name ... 1 High Street 2 Low Street ... I have another table of Employees: Employee_ID Store_ID Is_Active 1 1 Y 2 1 Y 3 1 N 4 2 N 5 2 N 6 2 Y I would like to count the number of

• Array Formula to Eliminate the Blank Cells in a Drop Down List 2015-01-18

Followed step by step a you tube video, Excel Magic Trick 916: Remove Blanks In Data Validation Drop Down List 5 Examples. When I hit control>shift>enter, then copy down, all my cells display the SAME name as the first cell, opposed to the DIFFERENT

• Convert an Array Formula's Text Results into a Usable Format 2015-10-13

When the results of an Array Formula are numbers, I find it generally easy to find an appropriate method to collapse the array into a single result. However when the results of an Array Formula are text, I find it difficult to manipulate the formula

• Excel Formula: An Array formula containing VLOOKUP, SMALL, IF, OR 2014-08-11

I am trying to create a formula that considers the data from three columns Y, H and V on my tab titled 'Data', returning the smallest value in A that meets that condition, once found Vlookup that smallest value and return the value from B. Below is w

• Excel 2007: Array formula lookup using INDEX and MATCH is only looking at first row 2011-12-05

I have a worksheet, "2011-11", with a series of numbers in column C. The numbers are each in a given currency, identified by a code in column B. What I want to do is write a single formula (in cell C14) that will convert each number (from C4:C13

• using an and() formula with an if statement in an array formula in excel 2013-01-11

OK so I have a list of client returns in a spreadsheet. Each client also has a risk category assigned to the (risky or safe) and the partner assigned to them (P1 or P2). Here is the an example of layout A B C D Client | Return | RiskCat | Partner ___

• How to use an array as a condition in a COUNTIFS or SUMIFS formula 2014-09-01

This question could be tricky to follow so I'll split it out with examples Given I have this table of overpriced fruits; Item Price Apple £0.80 Peach £1 Pear £1 Apple £1.20 Banana £1 Orange £1 Orange £1 Apple £4 I then have another table using the CO

• Course allocation with array formula in Google spreadsheet using constraints 2015-08-07

I try to assign students to courses based on the student's preference for each course and course capacity. Students are ranked based previous performance. In the current implementation, the data is obtained via Google Forms and other data sources. Th

• Why doesn't this array formula work when cell reference contains a formual? 2015-03-09

I have created an array formula (first formula below) to return the nth value in Data!B:B based on the date value in B4. The formulas appear to work when I have the date in B4 but if I use the second formula below to create a date it no longer works.