VLOOKUP & Array
I am trying to use the VLOOKUP feature an skip a column when I define my table array in the VLOOKUP formula. Is that possible?
For example, I have data in A2:J10. Because of data & formulas I have in column B through I, I don't want to include it in my table array in the VLOOKUP formula.
Here is my current formula that gives me a circular reference error: =vlookup($a$1,A2:J10,9)
However, I want to leave the data in column B through I and not move it to another part of the worksheet.
I want to use only column A and J as my table array.
Is it possible to define a non-contiguous range of cells as the table array?
Of course I can move my data around, but I wanted to find if there was a formulaic way to get what I need.
I am looking up first name and surname from a range with 2 vlookup functions, =vlookup(value,array,column)&vlookup(value, array,column) - what I get is JohnSmith - what I want is John Smith. Is there a way to insert a space? I have tried the "" and others.
I have been struggling with this problem for several days as I need to run it to add in for street codes and I learn vlookup from other examples given around.
I used vlookup on 1st cell A2. It works Ok. After that, I used the same formula, copy & paste to the rest of the cells. It comes out N/A error. Traced through, and turned out the formula ( lookup highlighted blue, table array is green) the highlight green area which table array supposed to be in has run/gone down to another cell ( from E1 to E2).
In all, how do I successfully duplicate & apply vlookup to other cells too? or can I "fixed" the highlighted table array not to run? I have tried =VLOOKUP(A6,$E6:$F10,2) putting $ sign on it, but it is still not correct.
Thank you very much for replying,
I have an array called "months" and for ex. it has the values;
And I have 12 arrays named: Jan, Feb. And the arrays has values, for ex;
I want to do this;
=vlookup("prt1", vlookup(1, months, 2, 0), 2, 0)
So, it will look for prt1 in array Jan and have a result as 2.
As you see, I want to use the result in the second vlookup as the name of the array for the first vlookup.
I can do this in a macro but I wonder how I can do this in a formula?
Was searching for an answer to this and saw a link in a post about non-array formulas, which got me 90% to the solution I need . but, I'm still not all the way there; hopefully someone can help.
I'm attempting to vlookup across multiple tabs, and am using the non-array recommendation (the array seems a little above my ability level!). The non-array IS working - I do get the data I expect, however the issue I'm having is that I get #N/As if no data is found, and I need to get a zero instead.
My current formula looks like this:
Any advice on how to get a zero?
Related Applications & Scripts
The XML to Array PHP script takes the hard work out of constantly developing custom PHP code to parse an XML document. A simple call the the XML to Array function with the appropriate repeating level will call your user defined function to process the XML data in a convenient, simple to use PHP array.
We've personally used this script to parse multiple XML and RSS feeds, and it has drastically decreased our development efforts. While there is no online demo, here is an example of what the script can do. The eBay RSS Store script utilizes the XML to Array script to parse the eBay RSS data feeds.
This class can be used to query arrays with a SQL-like syntax using LINQ. It takes an array as parameter and can perform several types of queries using an SQL-like syntax as if the array was a relational database. Currently it supports performing SELECT and DELETE queries. - Order by Support Added
VERY useful Contact / Feedback form for you website. You just need to drag & drop & modify the email address in php file & its ready to use.
- Drag & drop
- Nice & Professional look
- 100% Vector you can customize & resize.
- Email / Form Validation
- Send IP along with the message
- Commented code
- Help file Included
Note: This Contact form uses PHP which works on PHP enabled servers.
Simple Animation & 1 Line of code.
1. Open the flash file.
2. Change the URL & Tagline by simply selecting the button and pressing (F9)
3. The URL & Tagline are in quotation marks.
The jGallery WordPress Plugin gives you a simple and customizable way to create a gallery on any post, page, or sidebar. You have the option to use widgets, shortcodes, or to manually insert your gallery directory into the template files.
- Completely reworked the codebase from the ground up & backwards compatible.
- Removed timthumb script, now uses WordPress to create thumbnails automatically.
- Compatible with WordPress 3.0+ & tested with 3.2.1.
- Fixed the issue with jQuery 1.6+ breaking the scroll pane.
- Compatible with jQuery 1.4.2+.
- Better error checking on add and edit with AJAX check for existing gallery
- Removed wp_jgallery database table, now saves an array in wp_options.
- Returns cleaner HTML .
- Auto start will now pause on hover.
- Added icon to jGallery menu.