Vlookup alternative

[Kim]

Is there an alternative to vlookup formulas. I have below formulas and trying
to find a way to simplified it.

=VLOOKUP($A3,AllData,VLOOKUP(B$2,Sheet3!$A:$B,2,0),0)

I tried DGET but the problem is that it's only good for single row. I have
hundreds of row that use the same formulas.

Thanks.

 

[Roger Govier]

Hi Kim

I am assuming that row 1 of Alldata contains the list of values that might
appear in cell B2.
If so, then you could use
=VLOOKUP($A3,alldata,MATCH(B$2,INDEX(alldata,1,0),0))
Alternatively, create a named range for row 1 of alldata, e.g. Row1, and for
column A of alldata called ColA
then use
=VLOOKUP($A3,alldata,MATCH(B$2,Row1,0))

--
Regards
Roger Govier

Is there an alternative to vlookup formulas. I have below formulas and
trying
to find a way to simplified it.

=VLOOKUP($A3,AllData,VLOOKUP(B$2,Sheet3!$A:$B,2,0),0)

I tried DGET but the problem is that it's only good for single row. I have
hundreds of row that use the same formulas.

Thanks.

__________ Information from ESET Smart Security, version of virus
signature database 4510 (20091015) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4510 (20091015) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Dave Peterson]

Does the formula always go in the same column?

If yes, then the =vlookup(b$2...) portion is always looking at the same cell.

So instead of doing that calculation for each of your formulas, put that
=vlookup() portion in its own cell ($B$3 say).

Then you'll only have to determine that value once:
=VLOOKUP($A3,AllData,VLOOKUP(B$2,Sheet3!$A:$B,2,0),0)
becomes
=VLOOKUP($A3,AllData,$b$3,0)