VLOOKUP 2 ranges

[PartsPig]

I have 2 sheets exactly the same except for the title's. One tracks written checks and the other credit card usage. My input from left to right is Payee, date(autofills), Job number, invoice number, customer name, customer detail. I currently use VLookup to populate the last two columns based on the job number. Each sheet only looks at the range of itself however I would like them to look at the range of each other as well. Is this possible?

Current VLookup: =IF([@[RO '#]]<>"",IF(ISNUMBER([@[RO '#]]),VLOOKUP([@[RO '#]],E7958:H9959,3,FALSE),[@[RO '#]]),"")

RO is job number.
First it is confirming the RO is populated (not null) then confirming it is a number (if it is letters then I just populate these cells with the text) and if those pass then it will search for a match in the range.
Range is E:H (I currently have it looking at the last 2000 lines to find a match but it can look at all lines if necessary).
I have tried using range {E7958:H9959,'CC Tracking'!E:H} and {E:H,'CC Tracking'!E:H} and {'Check Tracking'!E:H,'CC Tracking'!E:H} including the { } but it always give the error below and always highlights only the first part of the argument which I have emboldened above. I have clicked the Help button but didn't find anything useful there.

Is it possible to search 2 different ranges on 2 different sheets or am I wasting my time?

 

[DanteAmor]

Try:

=LET(a,[@[RO '#]],IF(a<>"",IF(ISNUMBER(a),IFERROR(VLOOKUP(a,$E$7958:$H$9959,3,0),IFERROR(VLOOKUP(a,'CC Tracking'!E:H,3,0),"Dont exists")),a),a))

 

[DanteAmor]

Or:

=LET(a,[@[RO '#]],IF(a<>"",IF(ISNUMBER(a),IFERROR(VLOOKUP(a,VSTACK($E$8000:$H$10000,'CC Tracking'!$E$8000:$H$10000),3,0),"Dont exists"),a),a))

Or:

=LET(a,[@[RO '#]],b,$E$8000:$H$10000,SI(a<>"",IF(ISNUMBER(a),IFERROR(VLOOKUP(a,VSTACK(b,'CC Tracking'!b),3,0),"Dont exists"),a),a))

But with range in columns E and H, not the entire columns.

 

[PartsPig]

Or:

=LET(a,[@[RO '#]],IF(a<>"",IF(ISNUMBER(a),IFERROR(VLOOKUP(a,VSTACK($E$8000:$H$10000,'CC Tracking'!$E$8000:$H$10000),3,0),"Dont exists"),a),a))

Or:

=LET(a,[@[RO '#]],b,$E$8000:$H$10000,SI(a<>"",IF(ISNUMBER(a),IFERROR(VLOOKUP(a,VSTACK(b,'CC Tracking'!b),3,0),"Dont exists"),a),a))

But with range in columns E and H, not the entire columns.

So after studying LET and trying to digest what your methods were doing I concluded I could just use VSTACK in my range and leave the rest alone. Thanks Dante for showing me all the different ways this could be accomplished and introducing me to LET and VSTACK!!!

The final formula I went with that is working like a champ is:
=IF([@[RO '#]]<>"",IF(ISNUMBER([@[RO '#]]),VLOOKUP([@[RO '#]],VSTACK($E7967:$H9966,'CC Tracking'!$E$1:$H165),3,FALSE),[@[RO '#]]),"")

 

[PartsPig]

So after studying LET and trying to digest what your methods were doing I concluded I could just use VSTACK in my range and leave the rest alone. Thanks Dante for showing me all the different ways this could be accomplished and introducing me to LET and VSTACK!!!

The final formula I went with that is working like a champ is:
=IF([@[RO '#]]<>"",IF(ISNUMBER([@[RO '#]]),VLOOKUP([@[RO '#]],VSTACK($E7967:$H9966,'CC Tracking'!$E$1:$H165),3,FALSE),[@[RO '#]]),"")

After realizing the CC Tracking isn't growing as fast as the Check Tracking I changed the formula to a stagnate starting line of 3 on both sheets (the first line of each sheet with the info I need). This only added milliseconds at best which is of no concern to me.

The FINAL final solution:

=IF([@[RO '#]]<>"",IF(ISNUMBER([@[RO '#]]),VLOOKUP([@[RO '#]],VSTACK($E$3:$H9966,'CC Tracking'!$E$3:$H165),3,FALSE),[@[RO '#]]),"")

 

[DanteAmor]

This only added milliseconds at best which is of no concern to me.

The FINAL final solution:

Since you knew the LET function, you should apply it:

=LET(a,[@[RO '#]],IF(a<>"",IF(ISNUMBER(a),VLOOKUP(a,VSTACK($E$3:$H9966,'CC Tracking'!$E$3:$H165),3,FALSE)a),""))

You should also consider the option of using the IFNA function, in case the data does not exist in any of the 2 sheets, otherwise you will have the #N/A error:

=LET(a,[@[RO '#]],IF(a<>"",IF(ISNUMBER(a),IFNA(VLOOKUP(a,VSTACK($E$3:$H9966,'CC Tracking'!$E$3:$H165),3,FALSE),"Not exists"),a),""))

 

[PartsPig]

Since you knew the LET function, you should apply it:

=LET(a,[@[RO '#]],IF(a<>"",IF(ISNUMBER(a),VLOOKUP(a,VSTACK($E$3:$H9966,'CC Tracking'!$E$3:$H165),3,FALSE)a),""))

You should also consider the option of using the IFNA function, in case the data does not exist in any of the 2 sheets, otherwise you will have the #N/A error:

=LET(a,[@[RO '#]],IF(a<>"",IF(ISNUMBER(a),IFNA(VLOOKUP(a,VSTACK($E$3:$H9966,'CC Tracking'!$E$3:$H165),3,FALSE),"Not exists"),a),""))

Actually I want the #N/A error. That means it is a new customer and the information for those columns needs to be entered manually. After that it will be found going forward. However, I did start getting circular reference errors and the only way I could get around them was to enable iterative calculations. Not ideal but I guess since the range of one sheet was bigger than the range in the other sheet so it thought there was a circular reference??? I went through all the steps (select formulas tab, goto error checking, select drop down for circular references, ...) to rectify the error but there indeed was no circular reference that I could see so had to do something.

 

[DanteAmor]

Actually I want the #N/A error.

Maybe:

=LET(a,[@[RO '#]],IF(a<>"",IF(ISNUMBER(a),IFNA(VLOOKUP(a,VSTACK($E$3:$H9966,'CC Tracking'!$E$3:$H165),3,FALSE),"New customer"),a),""))

I did start getting circular reference errors

In which cell are you putting the formula?