LOOKUP Help Using non-First Column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RoadKill - 12 May 2008 23:32 GMT

Hello,

I am trying to pull data from the 5th column of a sheet as opposed to the
first. Here is my current formula: vlookup(A4,Sheet1!A1:T100,16,0).

What I want to do is change A4 to E4 of the same sheet, for these purposes,
Sheet1.

I don't think the VLOOKUP is a viable option so wanted to see if somone had
a better idea.

Thanks

Reply to this Message

Dave Peterson - 12 May 2008 23:48 GMT

I'm kind of confused, but if you're trying to bring back column P of Sheet1
based on a match in column E of Sheet1?

=index(sheet1!p:p,match(a4,e:e,0))

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

> Hello,
>
[quoted text clipped - 8 lines]
>
> Thanks

Signature

Dave Peterson

Reply to this Message

RoadKill - 13 May 2008 00:44 GMT

If I am using E4 though, this seems like it would be logical:
=index(sheet1!p:p,match(e4,e:e,0)). However, it just seems to pull the fourth
row of P instead of actually matching to E4 of Sheet2.

> I'm kind of confused, but if you're trying to bring back column P of Sheet1
> based on a match in column E of Sheet1?
[quoted text clipped - 20 lines]
> >
> > Thanks

Reply to this Message

Dave Peterson - 13 May 2008 01:38 GMT

I'm not sure what sheet holds the formula. I'm not sure what sheet holds E4.
And I'm not sure what sheet holds the column to match and column to retrieve.

Maybe...

=index(sheet1!p:p,match(sheet2!e4,sheet1!e:e,0))

If the formula is on sheet1, then you don't need the sheet1 references.
If the formula is on sheet2, then you don't need the sheet2 reference.

> If I am using E4 though, this seems like it would be logical:
> =index(sheet1!p:p,match(e4,e:e,0)). However, it just seems to pull the fourth
[quoted text clipped - 28 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message