If VLOOKUP is @NA check Table 2

[CordingBags]

I currently have a "formula" that requires five "helper" cells to produce a result.

Cell 1, =IF(F5+E5<1,"",VLOOKUP(BB5,'DONT TOUCH'!$C$1387:$D$3164,2,0)), - Does the LOOKUP value appear in Table 1
Cell 2, =IF(F5+E5<1,"",VLOOKUP(BB5,'DONT TOUCH'!$C$1:$D$1189,2,0)), - Does the LOOKUP value in Table 2

Cell BB5 contains sum of F5 and E5 to produce LOOKUP value , almost a sixth helper cell

Cell 3, =IFNA(BA5,""), - Is Cell 1 @NA
Cell4, =IFNA(BA6,""), - Is Cell 2 @NA
Cell 5, =CONCATENATE(BD5,BD6), - Concatenate contents of Cell 3 and Cell 4. for result

I am sure this could be contained within one cell if I can find the correct syntax.

Basic logic of a successful lookup,
Does the LOOKUP value appear in Table 1 if NOT apply same LOOKUP value to Table 2, if the LOOKUP value does not appear in either Table then return a blank, " ".
Conversely if the LOOKUP value appears in Table 1 show the data from column 2, or if the LOOKUP value appears in Table 2 then show the data in column 2.

In each case column 2 shows on which TAB the error is found (duplicate occurs).

On rare occasions the LOOKUP value could occur multiple times on either or both tables.
I appreciate in this scenario only the first "error" will report. Which needs to be cleared before the next will show.
I do not want nor expect a list of "error" entries.

Needs to work with Excel 2016.
This "formula" is replicated 66 times on each of a dozen tabs, very inefficient but does provide the answer.

Any help appreciated
Paul

 

[DanteAmor]

With a single formula, is it what you need?

varios 07ene2024.xlsm
	E	F	G																																																		BE
5	2	3																																																			res1
Sheet

Cell Formulas
Range	Formula
BE5	BE5	=IFNA(IF(F5+E5<1,"",VLOOKUP(BB5,'DONT TOUCH'!$C$1387:$D$3164,2,0)),"")&IFNA(IF(F5+E5<1,"",VLOOKUP(BB5,'DONT TOUCH'!$C$1:$D$1189,2,0)),"")

 

[CordingBags]

Hi Dante,

This looks like it should work, but unfortunately returns an entry from each part of the Lookup Table, as soon as valid entries are made in F5 and E5, even when there is no duplicate/nothing entered on any other tab within the Workbook.

I have managed to remove my helper cells with the following formulas, but it does require two cells. Each of which show where a duplicate is elsewhere in the workbook.
Perhaps having two is no bad thing if there are multiple errors.

One checks the top part of the lookup table, the other the bottom.

This is necessary as the data from the tab is itself included the the aggregated lookup table.
Which runs from row 1 to 3164 with each tab contributing about 200 rows of E5+F5 data.

=IF(SUM(E8+F8)<1,"",IF(ISNA(VLOOKUP(SUM(E8+F8),'DONT TOUCH'!$C$1:$D$1188,2,0)),"",VLOOKUP(SUM(E8+F8),'DONT TOUCH'!$C$1:$D$1189,2,0)))
=IF(SUM(E8+F8)<1,"",IF(ISNA(VLOOKUP(SUM(E8+F8),'DONT TOUCH'!$C$1387:$D$3164,2,0)),"",VLOOKUP(SUM(E8+F8),'DONT TOUCH'!$C$1387:$D$3164,2,0)))

Appreciate your interest and help.
Cheers

Paul

 

[CordingBags]

Eventually worked it out:

=IF(SUM(E5+F5)<1,"",IF(ISNA(VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1386:$D$3164,2,0)),IF(SUM(E5+F5)<1,"",IF(ISNA(VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1:$D$1188,2,0)),"",VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1:$D$1188,2,0))),VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1386:$D$3164,2,0)))

 

[DanteAmor]

Eventually worked it out:

=IF(SUM(E5+F5)<1,"",IF(ISNA(VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1386:$D$3164,2,0)),IF(SUM(E5+F5)<1,"",IF(ISNA(VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1:$D$1188,2,0)),"",VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1:$D$1188,2,0))),VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1386:$D$3164,2,0)))

That formula is not complete, it does not work for me.
If it works for you, then that's fine.

Otherwise, you could explain by example what results you want.

It is clear that if the sum of E5 + F5 is less than 1 then you want "", otherwise you want the results of the 2 tables.

• If the data to be searched exists in table 1, what do you want as a result?
• If it does not exist in table 1, what do you want as a result?
• If the data to be searched exists in table 2, what do you want as a result?
• If it does not exist in table 2, what do you want as a result?
• If the data exists in both tables, what do you want as a result?
• If it does not exist in any table, what do you want as a result.

If you answer each of those questions, we could have the result in a single simplified formula.

 

[CordingBags]

Hi Dante

Thanks for your continued interest, I have the formula in my workbook which seems to be working long may it continue.

To answer your comments:

If the data to be searched exists in table 1, what do you want as a result? Data from Table 1 column2. Column 2 indicates the TAB to check for the duplicate.
If it does not exist in table 1, what do you want as a result? Ignore table 1 and check table 2
If the data to be searched exists in table 2, what do you want as a result? Data from Table 2 column 2, which similar to table 1 column 2 indicates the TAB to check
If it does not exist in table 2, what do you want as a result? Return a blank for Table 2, Having already checked table 1 both would return a blank.
If the data exists in both tables, what do you want as a result? My understanding is only the first "find" will show, therefore this would be the entry from table 1, hence my earlier solution comment that having two formulas allows the first result from both tables to be returned. It is unlikely but possible that both tables would have multiple entries, but again only the first returns.
If it does not exist in any table, what do you want as a result. Blank

NB: Note my earlier question, Nested VLOOKUP I think, which tried to resolve this challenge by using non contiguous VLOOKUP table, which I understand is currently not possible.
The solution I received did not work "real time" with 66 entries per Tab to check against 15 other tabs.
Hence I added a helper TAB/Column which holds the data of E5 + F5 from each tab to form a contiguous lookup Table with the source TAB name being held in column 2.

Cheers

Paul

 

[DanteAmor]

According to your answers.

Try this:

=IF(SUM(E5+F5)<1,"",IFERROR(VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1386:$D$3164,2,0),IFERROR(VLOOKUP(SUM(E5+F5),'DONT TOUCH'!$C$1:$D$1188,2,0),"")))

 

[Joe4]

What is the point of this structure?

SUM(E5+F5)

seems unnecessary and redundant.

Should just use:

E5+F5

or

SUM(E5:F5)

Since both "SUM" and "+" are used to add, you only need one of them, not both.

 

[DanteAmor]

SUM(E5+F5)seems unnecessary and redundant.

It's correct, I didn't observe it, I just copied it from the op's formula.


In my initial answer I had it E5 + F5, but later I didn't check it.

Thanks @Joe4 and happy new year!

 

[Joe4]

It's correct, I didn't observe it, I just copied it from the op's formula.

Yes, I have been caught on that a few times myself!