| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hlookup-Drag to rows below
Thanks!
"Alojz" wrote: =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 "Carolina" wrote: Option 2 did the trick! Thanks for you prompt response and making my life easier. :) Carolina On an additional note, how can I add to this formula to return a "0" if the lookup value was a "0" instead of returning an N/A? "Alojz" wrote: 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 a 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. "Carolina" wrote: 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? |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HLOOKUP with variable rows | Excel Discussion (Misc queries) | |||
| Using VLOOKUP or HLOOKUP to sum ROWS | Excel Discussion (Misc queries) | |||
| Using VLOOKUP or HLOOKUP to sum ROWS | Excel Discussion (Misc queries) | |||
| Drag and Drop Rows (not just cells) | New Users to Excel | |||
| Can you drag/drop rows w/o using Cut/Paste | Excel Discussion (Misc queries) | |||
Threaded Mode
