Appending to existing function

[Guest]

Greetings,

I currently have a function I need to append to for a whole column:
=VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1)
=VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2)
=VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3)
" " " " " "

I need to append it to look like this:
=IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1))
=IF(I2<J2,100,VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2))
=IF(I3<J3,100,VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3))

Is there an easy method to appended the "=IF(I1<J1,100(existing data)" to
the existing function without changing current references?

I have 6 columns with over 200 rows that I need to apply similar appends too.

Thanks for any help.

 

[Niek Otten]

=IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!$AJ$4:$AT$140,AS1))

Just copy down as far as you need. All references will be adjusted, with the exception those with $ signs.

BTW, I can't imagine the "AS1" is correct. Shouldn't it just be 10; the relative column number in the search table?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Greetings,
|
| I currently have a function I need to append to for a whole column:
| =VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1)
| =VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2)
| =VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3)
| " " " " " "
|
| I need to append it to look like this:
| =IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1))
| =IF(I2<J2,100,VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2))
| =IF(I3<J3,100,VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3))
|
| Is there an easy method to appended the "=IF(I1<J1,100(existing data)" to
| the existing function without changing current references?
|
| I have 6 columns with over 200 rows that I need to apply similar appends too.
|
| Thanks for any help.
| --
| Just an ordinary Joe

 

[Guest]

Thank you very much!!! I was dreading the though of having to do a manual
change.

And to answer your question about the AS1 reference. This is correct, it is
yet another function that is used elsewhere on the worksheet.

 

[Guest]

Since you need the column index part, ie: AS1
to increment to: AS2, AS3 etc as you copy down from the top cell
think we could use: INDIRECT("AS"&ROW(A1)) in the top cell's formula
The ROW(A1) will act as the incrementer as you copy down

Try this slight tweak to Niek's suggestion:
=IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!$AJ$4:$AT$140,INDIRECT("AS"&ROW(A1))))
Copy down as required

 

[Max]

Sorry, kindly dismiss the earlier response ..
I must have been intoxicated (red-faced)
There wasn't any need to use INDIRECT at all

---