Get value from blank column

[Firdaus]

Dear Appsheet Team,

Can anybody help me for my problem 🙂

I have 2 (two) tables, Consumption and Settlement then i want to get value in the Settlement Table from Consumption Column, with this criteria :

- if on column Materials Before not blank in the Consumption Table i get value from this column in Settlement Table by Click Material ID (this i already get value), and

- if on column Materials Before blank i want get value from Material After from Consumption Table by Click Material ID (this i still not get value)

Before i say many thanks for anybody help my problem

Regards,

Firdaus

[Firdaus]

sorry i'm forgot for attach my formula

[jyothis_m]

IF(

ISNOTBLANK(LOOKUP(THISROW].[Material ID], "Consumption", "Material ID", "Materials Before"),

LOOKUP([THISROW].[Material ID], "Consumption", "Material ID", "Materials Before"),

LOOKUP([THISROW]. [Material ID], "Consumption", "Material ID", "Materials After")

)

[Firdaus]

Dear @jyothis_m ,

Thanks for your help, your formula success to solve case my problem

Regards,

Firdaus

[jyothis_m]

I think a more efficient method is to make Material ID Column as Type REF with Reference to CONSUMPTION TABLE. In which case the formula can be simplified as 

IF(

ISNOTBLANK([Material ID].[Materials Before]),

[Material ID].[Materials Before],

[Material ID].[Materials After]

)

[Firdaus]

for this very good suggestion, initially I wanted to do this but there is a condition where the user who inputs data is easier to remember the customer name than the material ID

[jyothis_m]

You can keep the key as [Material ID] column and make the Label any column of your choice or make a Virtual Column also concatenating required columns. The Label will show in the Drop-down instead of Key Column i.e. Material ID. But actually the Key will get stored in the sheet. 

[Firdaus · Accepted solution]

ok, thank you for the advice given and I will try it