Changing the Row_index_num via HLOOKUP

G

Guest

I have 2 linked worksheets sheet 1 and sheet 2. My formula existing in sheet
2 combines an IF, AND and HLOOKUP function and reads as follows:

IF(AND($W$3<'Sheet 2'!$M$5,HLOOKUP(P$8,'Sheet 2'!$M$8:$AV$103,4)>'Sheet
2'!$L11),'Sheet 2'!F11,"")

Now as I use my mouse to drag down this formula to cover several rows (same
column), the row numbers associated with cells L11 & F11 change accordingly.
However, my "Row_index_num" (4) remains constant. Is there a way to get this
number change in the same fashion?
 
B

Biff

Hi!

Try this:

=IF(AND($W$3<'Sheet 2'!$M$5,HLOOKUP(P$8,'Sheet
2'!$M$8:$AV$103,ROW(4:4))>'Sheet
2'!$L11),'Sheet 2'!F11,"")

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can the Row_Index_Num in HLOOKUP adjust relatively? 6
HLookup indirect multiple worksheets 5
vlookup and hlookup 4
HLOOKUP copied across rows with "row index number" changing 3
Creating a template that I can reproduce every month by keying ininformation from SAP 8
Averages-HLOOKUP?? 7
Conditional Arrays 1
Copying a cell ref. down a column, changing worksheet # along the 5

Top