Prev: using indirect to return a named range?
Next: Changing info from one worksheet to the next
From: Max on 9 Mar 2010 18:12
Here's my response in your earlier thread:

Addressing this line:
> Is there a way to still access the data in Column A in Sheet 1, even though
> VLOOKUP uses the range starting with Column B in Sheet 1?

Extend your horizon beyond vlookup, use index/match. Its much more
versatile, you can match on any col and "directly" return any other col to
the left or right of the match col, and accomplish this w/o having to fuss
around with col index numbers to boot.

Eg instead of : =VLOOKUP(B5,Sheet1!$B$5:$J$397,2,0)
Try this: =INDEX(Sheet1!C:C,MATCH($B5,Sheet1!$B:$B,0))
to return the same results as the vlookup

Just change the index bit: INDEX(Sheet1!C:C
to: INDEX(Sheet1!A:A
if you want to return the results from col A
(instead of col C)

Enjoy the breakthrough? wave it, hit YES below
--
Max
Singapore
---
From: Art on 9 Mar 2010 20:02
That was a ridiculously simple solution! Thank you so much...definitely a
tool that I will take advantage MANY times!!!

"Max" wrote:

> Here's my response in your earlier thread:
>
> Addressing this line:
> > Is there a way to still access the data in Column A in Sheet 1, even though
> > VLOOKUP uses the range starting with Column B in Sheet 1?
>
> Extend your horizon beyond vlookup, use index/match. Its much more
> versatile, you can match on any col and "directly" return any other col to
> the left or right of the match col, and accomplish this w/o having to fuss
> around with col index numbers to boot.
>
> Eg instead of : =VLOOKUP(B5,Sheet1!$B$5:$J$397,2,0)
> Try this: =INDEX(Sheet1!C:C,MATCH($B5,Sheet1!$B:$B,0))
> to return the same results as the vlookup
>
> Just change the index bit: INDEX(Sheet1!C:C
> to: INDEX(Sheet1!A:A
> if you want to return the results from col A
> (instead of col C)
>
> Enjoy the breakthrough? wave it, hit YES below
> --
> Max
> Singapore
> ---
From: Max on 10 Mar 2010 01:01
Welcome, do take a moment to hit YES in that earlier response
--
Max
Singapore
---
"Art" wrote:
> That was a ridiculously simple solution! Thank you so much...definitely a
> tool that I will take advantage MANY times!!!

 | 
Pages: 1
Prev: using indirect to return a named range?
Next: Changing info from one worksheet to the next