How to set multiple ranges in a vlookup formula? 3

[wec43wec]

When identifying a table_array in a vlookup formula, must the range be the exact start and end row/col?

In other words, can you set the row number to be an additional (extra) number of rows beyond the existing data range to take into consideration a larger data base (table_array) in the future?

 

[xlbo]

yes it can but no you don't want to

See here: faq68-1331

for how to use a dynamic named range to mean that you don't have to either keep changing the lookup range or extend it unnecessarily

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wec43wec]

Geoff - I am not sure that I totally understand the INDERECT and OFFSET function but it appears the FAQ68-1331 link refers to a table that only has data in one column.

It appears the COUNTA is counting the number of rows with data and the other COUNTA function is not counting columns or is counting (possibly) the number of columns containing data. Which is correct?

Question - Am I able to insert the formula (INDERECT and OFFSET) which is indiciated in the link, within my vLOOKUP formula? If so, this would allow me to set the table_array range in this manner.

I would need the solution to be in a non VBA format.

Thanks for the help !

 

[lionelhill]

If, in your application, your table may extend indefinitely downwards, but doesn't have any funny rows at the bottom that contain different sorts of data, you can also (of course) simply specify the columns and include no row-numbers whatsoever
for example, something like:
=vlookup(K1, A:G, 3, false)
This searches the whole of column A, so it breaks down if any cell could contain something you didn't want to search (for example a grand total at the bottom, or a header that could be mistaken for data)

 

[Gavona]

The solution I would go with is for the lookup to reference a named range and for that named range to be defined as in Geoff's post. But yes you could use Indirect and Offset directly in the formula.
(The named range approach does not work if the workbook containing the range is not open.)

Gavin

 

[wec43wec]

Gavona - I used a named range with the row number ending approx 25 empty rows below the actual data, it worked - thanks !

Lionelhill - your "=vlookup(K1,A:G,7,false)" format worked when I only had one condition in the vLOOKUP formula, but when I tried it (A:G) with another condition in the vLOOKUP formula - it did not work. Do you know why?

The formula I used is as follows:

=if(vlookup($E$6,Sheet1!A3:G100,7)=0."",vlookup($E$6,Sheet1!A3:G100,7,False))


Note: The data (table_array) and the report (output) is in the same file.

The number of columns will always be the same, however the number of rows could change (more or less).

Thanks

Thanks

 

[Gavona]

=if(vlookup($E$6,Sheet1!A3:G100,7)=0[red].[/red]"",vlookup($E$6,Sheet1!A3:G100,7,False))
You have a period rather than a comma.

Are you deliberately omitting the final argument in the first vlookup?

Regarding the named range:
Try using one of those formulae that Geoff referred you to. There are plenty of other explanations on the web. This one is pretty good:

http://www.ozgrid.com/Excel/DynamicRanges.htm

and there is also this:

http://support.microsoft.com/kb/830287

Gavin

 

[wec43wec]

Gavona - the period should have been a comma, my apologizes. I type it incorrectly in this post.

The actual formula has a comma.

 

[Gavona]

Try this:
=if(vlookup($E$6,Sheet1!A3:G100,7[red],False[/red])=0[red],[/red]"",vlookup($E$6,Sheet1!A3:G100,7,False))

Also this (based on lionelhill's suggestion):
=if(vlookup($E$6,Sheet1!A:G,7[red],False[/red])=0[red],[/red]"",vlookup($E$6,Sheet1!A:G,7,False))


Gavin

 

[Gruuuu]

Another option you may be interested in, wec43wec:
the INDEX/MATCH approach

     A      B      C
1  Label  Color   Word
2   One    Red    The
3   Two    Blue   Royal
4  Three  Green   House
5   Four  Orange  of
6   Five  Yellow  London

=INDEX(B:C,MATCH("One",A:A,0),MATCH("Word",1:1,0)-1)
[i]results in:[/i]"The"

INDEX returns the n,nth item in the table
MATCH returns the position of the value provided in the array

So my example looks for the position of "One" in column A, which happens to be the second item, and the position of "Word" in Row 1, which happens to be the third item (which I subtract one from). So within the Range B1:Cn it returns the second item in the second row of the table provided.

Make sense?

 

[wec43wec]

XLBO / Lionelhill - thanks for your help and suggestions. I gave both of you a star.

Gavona - the "false" and other suggestions you shared worked and will be how I will use this function in the future. As such, I gave you 2 stars.

Gruuuu - I am still trying to understand your formula, however it does not appear to apply to what I am trying to do, which is to use vLOOKUP to retreive data from the 7th column based on the LOOKUP key found in column A.

Would this (your formula) still apply - ?

 

[xlbo]

lionelhill - for reference, I would never suggest using full column references for formulae - especially lookups as they are relatively memory intensive. If you are using a complex workbook with lots of formulae, it will generate a lot of unnecessary calculation overhead

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Thanks for the star. I totally agree with Geoff (Xlbo) both about the use of full column references and the ways of dealing with ranges that change size.

Gruu's formula: Index and match is an alternative to vlookup.
Match returns the row number and index uses this to retrieve the data from the column. The approach has several advantages.

To understand the formulae it is often better to set up a little example as Gruu did than work with real, more complex data. You could readily explore the use of True/False in vlookup for example. I would also suggest putting the different elements of Gruu's formula into different cells so you can see what each function returns.
=MATCH("One",A:A,0)
=MATCH("Word",1:1,0)

The concerns about referencing entire columns or rows applies equally to the Index and Match approach.


Gavin

 

[Gruuuu]

concerning the full column referencing:

If either your vlookup or match specifies a complete match, the calculation will stop once it reaches its target.

The concern comes in when it CAN'T find the target, and then searches the rest of the row/column.

In cases where those occurances are few, or if the workbook is relatively small, I usually don't worry about it, because it's a hassle to either dynamically size my ranges, or assume a maximum threshold on my data (assumptions resulting in that age-old addage).

 

[SkipVought]

What VERSION of Excel?
[tt]
1. Use Insert > Name > Create -- Create Names in TOP ROW.

If prior to 2007....

2. Data > List > CREATE. This creates a TABLE that as you add rows, the Named Ranges of each column expand with the table.

If 2007 or greater....

2. Insert > Tables > Table. Same effect as above.
[/tt]
Then use INDEX & MATCH rather than VLOOKUP. This is my SOP!

I also extinsively use TABLE REFERENCES, which is new to 2007 TableName[Field Name]. Pretty powerful!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

(1) Thanks Gruuu (and others) for explanation of full-column referencing. My spreadsheets are invariably smallish, and I never use a lookup to find something that isn't there, so I'm not usually bothered... but it's always good to consider the future, remain scalable, and be aware of one's weaknesses.

(2) wec43wec, one of the advantages of index/match is you can do the look-up on any column you want, whereas vlookup is always limited to the left-most column.

(3) Skip, thanks for tip about table references.

 

[xlbo]

the other advantage of Index/Match is that it is actually (very slightly) faster than vlookup...I still use vlookup though - less typing and it forces better design of spreadsheets in general ("keys" to the left of the datasets)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question