Index & Match versus V & H lookups

[Guest]

Hi all

Thanks for the previous replies, I have a new question

I'm led to believe that, V&H lookups are a volatile formula and that index and match are not

I was wondering if someone could post an alternative to these 2 formulas, so that I can learn and understand them

(HLOOKUP(V5,($AB$4:$AK$12005),ROW(2:2),0



(VLOOKUP(G5,$a$4:$F$200,4,FALSE

Many thank

Geron

 

[Aladin Akyurek]

They are not volatile in the sense that OFFSET() or NOW() are.

INDEX/MATCH together have a larger scope, meaning it can replace ordinary
lookup formulas with VLOOKUP, HLOOKUP, and LOOKUP (the latter not always).
That said:

=HLOOKUP(V5,($AB$4:$AK$12005),ROW(2:2),0)

becomes:

=INDEX($AB$4:$AK$12005,ROW(2:2),MATCH(V5,$AB$4:$AK$4,0))

although not happy with ROW(2:2) [ Better: ROW()-ROW($A$2)+2, if $A$2 is the
first cell where this formula is entered. ].

=VLOOKUP(G5,$a$4:$F$200,4,FALSE)

becomes:

=INDEX($D$4:$D$200,MATCH(G5,$A$4:$A$200,0)

Hi all,

Thanks for the previous replies, I have a new question:

I'm led to believe that, V&H lookups are a volatile formula and that index and match are not.

I was wondering if someone could post an alternative to these 2 formulas,

so that I can learn and understand them:

 

[Guest]

Thanks Aladin, your a champion.

One last thing, in the =INDEX($D$4:$D$200,MATCH(G5,$A$4:$A$200,0) what tells it to look in the 4th column ?

Thanks again

Geron

----- Aladin Akyurek wrote: -----

They are not volatile in the sense that OFFSET() or NOW() are.

INDEX/MATCH together have a larger scope, meaning it can replace ordinary
lookup formulas with VLOOKUP, HLOOKUP, and LOOKUP (the latter not always).
That said:

=HLOOKUP(V5,($AB$4:$AK$12005),ROW(2:2),0)

becomes:

=INDEX($AB$4:$AK$12005,ROW(2:2),MATCH(V5,$AB$4:$AK$4,0))

although not happy with ROW(2:2) [ Better: ROW()-ROW($A$2)+2, if $A$2 is the
first cell where this formula is entered. ].

=VLOOKUP(G5,$a$4:$F$200,4,FALSE)

becomes:

=INDEX($D$4:$D$200,MATCH(G5,$A$4:$A$200,0)

 

[Guest]

this

$D$4:$D$200

 

[Aladin Akyurek]

Yes. Given the lookup table/area in your original formula, it's the D-range
which is the 4th column of your lookup table.

 

[Guest]

Aladi

Thanks again for taking the time to respond, I really appreciate your help

Geron