Hlookup-Drag to rows below


C

Carolina

I have a numbers in cells CA-CF and then results on DV-EE and goes from row 3
(header) to row 199.
For a plain example I want to use hlookup to look up the number in column CA
and return the value from the column containing the same number. I.e. I want
the result to for the 5 lookup to be $1,575.

CA DV DW DX DY DZ
5 $275 $550 $825 $1,200 $1,575

I managed to pull the following formula and it works
=HLOOKUP(CA4,$DV$3:$EE$199,2,FALSE)

....but now I want to drag it to all subsequent rows but when I do the 2
stays the same....it should be 3, 4, 5, and so on....is there a way of
automatically dragging the formula to the following rows taking the
respective row data?
 
Ad

Advertisements

A

Alojz

2 stays the same because u did tell the formula having 2 in there :).
There are several possibilities how to solve it. Two of them are:
1. fill one column with numbers 1,2,3 etc. and reference ur formula to this
column to look up the different rows.
2. even more efficient is to rebuilt the formula as:
=HLOOKUP(CA4,$DV$3:$EE$199,row(CA4)-2,FALSE), drag and copy down

e.g. for CA4 it will look up in row 2, for CA5 in row 3, for CA5 in row 4
and so on.
 
C

Carolina

Option 2 did the trick! Thanks for you prompt response and making my life
easier. :)
Carolina
 
A

Alojz

=IF(ISNA(HLOOKUP(CA4,$DV$3:$EE$199,row(CA4)-2,FALSE)),0,HLOOKUP(CA4,$DV$3:$EE$199,row(CA4)-2,FALSE))

instead of 0 i would prefer "" in the formula to leave the cell blank

cheers,
Alojz
 
C

Carolina

Thanks!

Alojz said:
=IF(ISNA(HLOOKUP(CA4,$DV$3:$EE$199,row(CA4)-2,FALSE)),0,HLOOKUP(CA4,$DV$3:$EE$199,row(CA4)-2,FALSE))

instead of 0 i would prefer "" in the formula to leave the cell blank

cheers,
Alojz
 
Ad

Advertisements

Joined
Mar 15, 2018
Messages
1
Reaction score
0
Hi Sir,

My formula is as below, i am unable to drag the formula for multiple rows. Please help!

=HLOOKUP(B1:H1,Table1[[#Headers],[#Data],[Description]:[Total (INR)]],2,0)
 
Ad

Advertisements


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

Top