LOOKUP Help Using non-First Column

[RoadKill]

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

 

[Dave Peterson]

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,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

 

[RoadKill]

If I am using E4 though, this seems like it would be logical:
=index(sheet1!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.

 

[Dave Peterson]

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,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.