Refer to current row in formula
 

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.

:o:


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98040

Refer to current row in formula
 

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

?

On 19 Maj, 15:06, willemeulen
wrote:
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.

:o:

--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile:http://www.thecodecage.com/forumz/member.php?userid=285
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=98040

Refer to current row in formula
 

Jarek Kujawa;350460 Wrote:
=HLOOKUP(L3,Sheet4!B2:AE35,ROW(),FALSE)

Does not seem to work Kajawa :(


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98040

Refer to current row in formula
 

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

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"willemeulen" wrote in message
...

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.

:o:


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile:
http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98040

Refer to current row in formula
 

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

--
Regards
Roger Govier

"willemeulen" wrote in message
...

Jarek Kujawa;350460 Wrote:
=HLOOKUP(L3,Sheet4!B2:AE35,ROW(),FALSE)

Does not seem to work Kajawa :(


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile:
http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98040

Refer to current row in formula
 

willemeulen wrote:
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.

:o:



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.