Refer to current row in formula

[willemeulen]

I want to do a Hlookup which has to return the value from the row it's
standing in.

=HLOOKUP(L3,Sheet4!B2:AE35,????,FALSE)

L3 gives a code which is on top of the lookup table, the answer can be
found by returning the value in the row.

Another option would be index/match function like this:

=INDEX(Sheet4!B3:Z55,MATCH(L3,B3:B55,0),MATCH("ROW",B3:Z3,0))

If it's not possible to refer to the row I could create an extra column
(which I hide) to use in the index match function.

:

 

[Jarek Kujawa]

=HLOOKUP(L3,Sheet4!B2:AE35,ROW(),FALSE)

?

 

[willemeulen]

=HLOOKUP(L3,Sheet4!B2:AE35,ROW(),FALSE)

Does not seem to work Kajawa

 

[Bernard Liengme]

If I understand the question correctly
=HLOOKUP(L3,Sheet4!B2:AE35,ROW(),FALSE)

best wishes

 

[Roger Govier]

Hi

What do you mean when you say it doesn't work?
Does it give an answer - but no the answer you expect?
Does it give an error value?

Jarek's suggestion is correct, for the question you asked.
If the formula were entered in say cell A10 of Sheet1, then it would return
the value from Row 11 of Sheet4, for the Column value that appears in Row2
of that sheet, which matches the value in Cell L3 of Sheet1

Can you post a small sample of the data on Sheet4, and what you have entered
in L3

 

[Glenn]

I want to do a Hlookup which has to return the value from the row it's
standing in.

=HLOOKUP(L3,Sheet4!B2:AE35,????,FALSE)

L3 gives a code which is on top of the lookup table, the answer can be
found by returning the value in the row.

Another option would be index/match function like this:

=INDEX(Sheet4!B3:Z55,MATCH(L3,B3:B55,0),MATCH("ROW",B3:Z3,0))

If it's not possible to refer to the row I could create an extra column
(which I hide) to use in the index match function.

:

Something like this should work (assuming I'm understanding you correctly):

=INDEX(Sheet4!$B$2:$AE$35,ROW()-1,MATCH(L3,Sheet4!$B$2:$AE$2,0))

You may need to adjust the "-1" depending upon what row you are putting this
formula in.

If this doesn't work correctly for you, something more than "didn't work" (see
your response to Jarek Kujawa) will be necessary to provide more assistance.