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?

 

[PartsPig]

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),""))

In which cell are you putting the formula?

The formulas are in both of the customer columns with customer name returning 3 and customer detail returning 4. Keep in mind there are 2 sheets and each has its own version of the formulas. The first sheet has 9970 lines currently and the second 166 so the formulas in the customer name column look like this:

First sheet:
=IF([@[RO '#]]<>"",IF(ISNUMBER([@[RO '#]]),IFNA(VLOOKUP([@[RO '#]],VSTACK($E$3:$H9970,'CC Tracking'!$E$3:$H670),3,FALSE),"New Customer"),[@[RO '#]]),"")

Second sheet:
=IF([@[RO '#]]<>"",IF(ISNUMBER([@[RO '#]]),IFNA(VLOOKUP([@[RO '#]],VSTACK('Check Tracking'!$E$3:$H15155,$E$3:$H166),3,FALSE),"New Customer"),[@[RO '#]]),"")

In each I have the range referencing the sheet the formula is on set to the line immediately before the line the formula is on. And the range referencing the other sheet set to a few hundred more than that sheet actually has since the sheets do not grow at the same pace. I guess ideally the formula would calculate the exact range by counting the lines or something??? but I don't know how to do that, yet lol. I tried setting the formulas to the exact range manually but still got the circular reference error so setting them to a few hundred more is not the cause. I like the idea of adding "New customer" to the formula so others will know exactly what's going on rather than just getting #N/A error and as you can see in the formulas above I have already added the IFNA() check to them.

 

[DanteAmor]

In which cell are you putting the formula?

I mean if it is in cell E5 or F6 or G7. Exactly in which cell? Could you answer?
If the formula is within the same search range, that's why you have the circular reference.