LOOKUP

[ruta]

Hi all,

I have an issue with lookup with two not related tables. The idea is simple: one 'small' sheet contains a list with few invoice numbers that are exceptions and the other-'big' sheet - is a table with many invoice numbers. I need to check if an invoice number from the big sheet is also in the small sheet and if so I need the value to be equal to Y (as in yes exception) and N if the invoice is only in the big sheet.  How do I connect the two tables so this works?

Many thanks!!

[Anonymous · Accepted solution]

Create a column in the big sheet and name it something like "Is Exception." Then type the formula:

Is Exception =
var __currentInvNum = BigTable[InvoiceNumber]
var __isException = 
	NOT ISEMPTY(
		FILTER(
			SmallTable[InvoiceNumber],
			SmallTable[InvoiceNumber] = __currentInvNum
		)
	)
return
	__isException

Please note the above is not a measure. It's a formula for your column in the big table. Now that you have this, you can hide the small table from view.

Best

Darek

[ruta]

Thank you so much @Anonymous !! Would never have figured this out on my own.

all best,

ruta