Referencing a Row value from within a VLOOKUP

[Michael Behm]

Hello group,

I've worked with excel for many years, but I could use some help throwing
this together. The background:

I have two sheets within the same workbook. One sheet has check numbers and
values that have been issued by the company. The other sheet has that same
information, but only what has been reported back to us by the bank. I
working on streamlining the comparison between the two sheets to more easily
see what checks are outstanding (have not been cashed at the bank). Also,
if the check has been cashed, I want to compare the value that the bank
recorded with the value that our company recorded to insure that they match
up.

My problem is that the table is going to grow as the year goes on and I'm
not sure how to get the VLOOKUP table array to grow with it. In the
following formula, F5 is the cell that contains the check number on our
company detail sheet, Bank DetailB6:C17206 is the the table array where the
banks check number and value is stored:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No
Match","Match"))

Is there a way to use the indirect function and/or the Row function to
update the row reference to 17206 (the footer row in my bank detail sheet?
This way, as more bank detail is added, that row reference will remain at
the bottom of the list.

I know the syntax doesn't work, but I would like it to do this:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
Detail'!C17206),1,FALSE)),"No Match","Match"))

Maybe there's a better way to go about this. I'm open to suggestions if the
community has any. Right now, each sheet has the following columns:

1.) Check Number
2.) Value
3.) Match? - if the check number is found on the other sheet, "Match" is
entered in to the cell, otherwise "No Match" is entered
4.) Amount of non-matching Checks - If Cell "Match?" = "No Match", Value
amount is entered into the cell
5.) Amount of Matching Check from other sheet if Values differ - If a check
number match is found on the other sheet, this cell compares the two values
and return the other sheets value only if the two values don't match.


Any help/comments/suggestions will be appreciated.

Michael

 

[RagDyeR]

What problem do you anticipate having if you would simply oversize the
table, to take into consideration future expansion?

I don't think that an extra 1 or 2,000 rows would hurt.

What are your concerns about this?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Hello group,

I've worked with excel for many years, but I could use some help throwing
this together. The background:

I have two sheets within the same workbook. One sheet has check numbers and
values that have been issued by the company. The other sheet has that same
information, but only what has been reported back to us by the bank. I
working on streamlining the comparison between the two sheets to more easily
see what checks are outstanding (have not been cashed at the bank). Also,
if the check has been cashed, I want to compare the value that the bank
recorded with the value that our company recorded to insure that they match
up.

My problem is that the table is going to grow as the year goes on and I'm
not sure how to get the VLOOKUP table array to grow with it. In the
following formula, F5 is the cell that contains the check number on our
company detail sheet, Bank DetailB6:C17206 is the the table array where the
banks check number and value is stored:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No
Match","Match"))

Is there a way to use the indirect function and/or the Row function to
update the row reference to 17206 (the footer row in my bank detail sheet?
This way, as more bank detail is added, that row reference will remain at
the bottom of the list.

I know the syntax doesn't work, but I would like it to do this:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
Detail'!C17206),1,FALSE)),"No Match","Match"))

Maybe there's a better way to go about this. I'm open to suggestions if the
community has any. Right now, each sheet has the following columns:

1.) Check Number
2.) Value
3.) Match? - if the check number is found on the other sheet, "Match" is
entered in to the cell, otherwise "No Match" is entered
4.) Amount of non-matching Checks - If Cell "Match?" = "No Match", Value
amount is entered into the cell
5.) Amount of Matching Check from other sheet if Values differ - If a check
number match is found on the other sheet, this cell compares the two values
and return the other sheets value only if the two values don't match.


Any help/comments/suggestions will be appreciated.

Michael

 

[Michael Behm]

Two concerns I have:
1.) With the current calculations, the workbook is already not performing
very fast. I don't know if adding extra rows to the formula will further
degrade it.
2.) I am not the one who will be using this sheet. It worries me to make
assumptions about how much the sheet will grow. If I underestimate, then
things will need to be re-worked in the future.

Granted, by adding 10000 rows to the formula, it would probably be covered,
but do you guys think that will affect the performance at all?

Thanks for your reply RD

 

[Alex]

I don't specify rows at all. Try this:

=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B:$C,1,FALSE)),"No
Match","Match"))

I wouldn't worry about looking values up in the first 5 rows of your
range. When will your lookup value ever be the same as the title of
your other page?

Hope this helps,

Alex.

 

[Michael Behm]

Thanks RD and Alex for you posts. I think I will just go with your
suggestion Alex and see how it runs. It probably won't make a big
difference on the system resources anyways.

Michael

 

[RagDyer]

You didn't bring up your problem with calc efficiency in your OP,

*BUT* Alex's formula is referencing 2 *entire* columns of over 65,000 rows
each!

On another note, can you explain the concept of your formula to me?
You're referencing *2* columns in your Vlookup formula, but only using the
first column for lookup *and* return purposes.

Vlookup is supposedly a "not too efficient" function in the first place.

Try this formula, and see if there's any improvement:

=IF(F5="","",IF(ISNA(MATCH(F5,'Bank Detail'!$B$6:$B$17206,0)),"No
Match","Match"))

Also, post the other formulas your using in the other columns.