Index/Match VS VLOOKUP 2

[joel009]

I finally got a VLOOKUP to work:
=VLOOKUP(D4,PREVIOUS!D4:AN115,37,FALSE)
This brings in the correct value.

But since I have heard the Index and Match combined is a better method I am trying to figure out why this does not work:

=INDEX(PREVIOUS!A4:AN115,MATCH(GE!D4,PREVIOUS!D4115,0),PREVIOUS!AN4:AN115)

It's on the same sheets, same data.

Also, how do I increment only the compare cell with an auto fill? When I copy down the column all the Row/Column values increase as well as the compare cell.



Joel

 

[joel009]

OK This works for the Index/Match:
=INDEX(PREVIOUS!A4:AN115,MATCH(GE!D4,PREVIOUS!D4115,0),40)

but when I copy down the formula it changes the ranges:
=INDEX(PREVIOUS!A5:AN116,MATCH(GE!D5,PREVIOUS!D5116,0),40)

How do I keep the range the same and only change the Match column?

Thanks in advance for any idea(s)

Joel

 

[hjgoldstein]

$D$4:$D$115

Aspiring to mediocrity since 1957

 

[joel009]

=INDEX(PREVIOUS!A5:AN116,MATCH(GE!D5,PREVIOUS!D5116,0),40)

Changed to:
=INDEX(PREVIOUS!$A$4:$AN$115,MATCH(GE!D4,PREVIOUS!$D$4:$D$115,0),40)

When incremented by autofill on the next line gives this:
=INDEX(PREVIOUS!$A$4:$AN$115,MATCH(GE!D5,PREVIOUS!$D$4:$D$115,0),40)

The $ makes Excel handle the value like a string or a literal.

Pretty sweet!

Joel

 

[joel009]

Now the question becomes how to add the $ character to my range.

Anybody know an easy way? I'm thinking looping through the range string and adding them.

Joel

 

[byundt]

Joel,
The F4 key cycles through the various combinations of absolute and relative references, e.g. AN1, AN$1, $AN1, $AN$1. You may find this helpful when building a formula by pointing at cells.

If you have designed your formulas correctly, they can be copied across and down while keeping the desired row and column references fixed. I suggest this approach rather than looping through the range.

Although you can certainly write a macro to add the $ to your formulas, the trick will be to get it to be specific enough. I personally suggest using the Home...Find and Select...Replace menu item instead. With it, you can do the editing on an entire range of cells at once.

Brad

 

[SkipVought]

The $ makes Excel handle the value like a string or a literal.

The $ has nothing to do with that!

It has everything to do with relative & absolute references. Check help for more insight. Brad did a nice job explaining part of that.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I would also highly recommend using Named Ranges or, if you have Excel 2007+, Structured Tables & Structured Table References. It will 1) make coding you formulas easier and 2) make your formulas more understandable.

I rarely use A1 reference notation any more and I construct very complex, multi-sheet workbooks.

Your VLOOKUP() formula might look something like this in one of my workbooks...
[tt]
=INDEX(tPrev[Product Name],MATCH(LookupVal,tPrev[Product ID],0),40)
[/tt]
Where tPrev is a table perhaps on another sheet and LookupVal is a Named Range. It's ALOT more readable and understandable AND much MUCH simpler and easier to code!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

Skip probably meant to post a VLOOKUP using structured references without the column index; tPrev[Product Name] already identifies a specific column. This feature is one of the big advantages of using structured references.
=INDEX(tPrev[Product Name],MATCH(LookupVal,tPrev[Product ID],0))

Again, if you have Excel 2007 or later, you can use IFERROR function to trap an entry that isn't in the table and avoid an error value as the result:
=IFERROR(INDEX(tPrev[Product Name],MATCH(LookupVal,tPrev[Product ID],0)),"Not found")

Brad

 

[SkipVought]

Sorry, I had 2things going on in my head, but the most important of them, IMNSHO, came out, because with INDEX() & MATCH() you need not worry about the lookup range being on the leftmost column of the lookup range, as is required by the VLOOKUP() function!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joel009]

The explanations explain why this does not work:
ActiveCell.Formula = "=INDEX(" & rngPreviousShtRange & ",MATCH(" & strSearchCell & "," & rngPrevShtMatchCol & ",0)," & strDestinationColOffsetfromSearchColumn & ")"

or this:
=INDEX(PREVIOUS!A4:AN115,MATCH(GE!D4,PREVIOUS!D4115,0),PREVIOUS!AN4:AN115)

with Skip's explanation:

=INDEX(tPrev[Product Name],MATCH(LookupVal,tPrev[Product ID],0),40)

tPrev[Product Name] = PREVIOUS!A4:AN115 but I forgot to name the field [Product Name]

MATCH(LookupVal,tPrev[Product ID] = MATCH(GE!D4,PREVIOUS!D4115 but I did not name the field again.

This is possibly becoming clearer....LOL

QUESTION = Would you be able to store the whole expresion as a string? I tried this but I think the range objects would not let me.

I am really new to Excel VBA and these ranges are eluding me.



Joel

 

[joel009]

I have it working manually in 2 columns.
The index method:
=INDEX(PREVIOUS!A4:AN115,MATCH(GE!D4,PREVIOUS!D4115,0),40)
VLOOKUP;
=VLOOKUP(D4,PREVIOUS!D4:AN115,37,FALSE)

Both pull in the correct value with the formula in the GE spreadsheet.

with Skip's code:
=INDEX(tPrev[Product Name],MATCH(LookupVal,tPrev[Product ID],0),40)

He names the col for the Index yet my code works but I do not name a column. Did I just get lucky or??????



Joel

 

[byundt]

If tPrev is the name of a Table, then the table range might be PREVIOUS!A4:AN115.

tPrev[Product Name] on the other hand would be a particular column within that table--the one whose caption is Product Name.

You should not specify column 40 in this formula:
=INDEX(tPrev[Product Name],MATCH(LookupVal,tPrev[Product ID],0),40)
tPrev[Product Name] is a single column, so you can't look for the 40th column in that range

It could be:
=INDEX(tPrev[Product Name],MATCH(LookupVal,tPrev[Product ID],0)) You don't need the 40 here because tPrev[Product Name] already specifies the column

Or perhaps:
=INDEX(tPrev,MATCH(LookupVal,tPrev[Product ID],0),40) You need the 40 here because the first parameter is the entire Table

 

[SkipVought]

You must be very careful using a range like A4:AN115. If your table expands, your formula will not consistently return valid results. It is another reason that using Structured Tables or dynamic Named Ranges proves a significant improvement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joel009]

Brad - I think this scenario is what is happening with the 40:

Or perhaps:
=INDEX(tPrev,MATCH(LookupVal,tPrev[Product ID],0),40) You need the 40 here because the first parameter is the entire Table

But the 40, from what I can deduce, is the offset for the return column or value returned if the Match is found in the Index. That is the way it is working according to my meek interpretation. But possibly you are trying to tell me that I do not need to refrence the whole table, just the match column?
Or possibly:=INDEX(PREVIOUS!D4115,MATCH(GE!D4,PREVIOUS!D4115,0),40)???
But I would still need the 40 to pull in the column data by the Match?

Skip - I do not want to hard code in anything. I need to determine the dimensions of tPrev dynamically but I was using this manual process to try to understand it more. I am checking into Structured Tables or dynamic Named Ranges. It really is the way I want this to end up.

The $ has nothing to do with that!

It has everything to do with relative & absolute references. Check help for more insight. Brad did a nice job explaining part of that.

As you can see my terminology is something I need to enhance.

Joel

 

[joel009]

No Good. Changing my formula to:=INDEX(PREVIOUS!D4115,MATCH(GE!D4,PREVIOUS!D4115,0),40) results in a Ref error.
Is it possible Skip/Brad that the return value your formulas would produce is True/False or a cell number?

I am missing something here still, sorry but thanks for your help.

Joel

 

[SkipVought]

Since you reference ONE COLUMN (D) in INDEX...
[tt]
=INDEX(PREVIOUS!AN4:AN115,MATCH(GE!D4,PREVIOUS!D4115,0),1)
[/tt]

If you reference a multicolumn reference range, then you must supply the column number relative to the multicolumn reference range.

40 is column AN in the reference A:AN.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joel009]

BTW - I have been assured by my managers that using relative & absolute references will not affect the accuracy of the data that is returned. I am still trying to wrap my head that. I do not beleive it but I am supposed to code under that assumption.

Joel

 

[SkipVought]

I have been assured by my managers that using relative & absolute references will not affect the accuracy of the data that is returned.

"...using relative & absolute references appropriately and correctly will not affect the accuracy of the data that is returned." In fact, it will assure that the correct data is returned!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joel009]

Skip - Regarding your last post: then if I wanted to return the same coumn 40 as the result to my formula (What I want to do) how would I represent that with the single column reference like:=INDEX(PREVIOUS!D4115,MATCH(GE!D4,PREVIOUS!D4115,0),40). I left the 40 in and it returns a Ref error.

Joel