HLOOKUP - simple way for return-row to be the same row as formula row?

Question

Level 2

380 points

HLOOKUP - simple way for return-row to be the same row as formula row?

hi

using HLOOKUP, is there a simple way for the return-row to be the same row as the formula, instead of writing in the row number?

i have a huge number of rows, each with the same formula, each checking if "selected project" appears in the header column.... they then pull the value from the same row from that particular column.

it will take ages to type in the row number for every formula, so i was hoping there was a way to define the same row, so i can just copy-paste the same formula on each row?

cheers

ric

MacBook Pro (15-inch, Late 2016), macOS Sierra (10.12.5)

Posted on Mar 26, 2018 11:56 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,867 points

Mar 27, 2018 12:45 AM in response to ric frankland

Hi ric,

HLOOKUP always searches in the top row of the lookup table, and returns from the nth row, where the row number is a count of rows starting with the search row.

Assuming your search row is row 1, and the formula is in rows 2 to whatever, your formula should resemble the following example:

The ROW() function returns the row number of the row containing the function. In this case that number is coincides with the row number of the lookup table from which we want data returned in this row of the main main table, which contains the formula.

Regards,

Barry

Reply

Answer 2

ric frankland Author

Level 2

380 points

Mar 27, 2018 12:45 AM in response to Barry

excellent, thanks Barry!

perfect, it was the ROW() function i forgot all about :-)

ric

Reply

HLOOKUP - simple way for return-row to be the same row as formula row?

Similar questions