Handling Variable Number of Rows in Lookup and Index

[mhp620]

Hi... I think this will require VBA..

I need a function that will look up the entries of Sheet1-Column-A in Sheet2-Column-A. If an entry in Sheet1-Column-A exists in Sheet2-Column-A, then I need to copy Sheet2-Column-D and Sheet2-Column-E into Sheet1-Column-D and Sheet1-Column-E. I need to do this for each row in Sheet1-Column-A with data.

The problem I'm not sure how to handle is that the number of rows in each of Sheet1-Column-A and Sheet2-Column-A will change from week to week.

Suggestions on how to handle this would be greatly appreciated.

Thanks. Mike

 

[Gerald Higgins]

When you say "copy", do you actually mean "return the values from" ?
I'm assuming you do, because you referred to LOOKUP and Index in your thread title, and that's what those functions will do.
So, in that case, you can use references like D:D to refer to the whole of Col D, or even D:E to refer to the whole of Cols D and E.
There are some functions where you can not use this type of reference, but you can for VLOOKUP and INDEX.

There might be better ways of doing this, but this should work . . .