# if , any nested formula

• hi
i have 3 lists, i want them validated but shown in one cell.
col. A have 5 bill types, (Validated "Bill")
col. B have 28 product types, (Validated "product")
col. C have 12 material types, (Valided "Material")

i want in
F2 to select any of the 3 lists.
G2 to recognise them by the validation title.
H2 to categorise them payable or receivables:

attached is the example.
Thank you

## Files

• Re: if , any nested formula

I don't understand the results you show. There is nothing in F2, G2 or H2. Are you looking for two data validations? The first picking from Bills, Products, Materials and the second picking from the corresponding lists in columns A to C?

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: if , any nested formula

may be i didnt know how to express what i want.
attached is a revised excel sheet.
i am unable to use "if" formula to select any item of a list

## Files

• Re: if , any nested formula

If the example is actually how you intend setting up the worksheet then I would suggest that you rethink and use a proper data layout.. I would also suggest that you format the data as a Table.

You can't have 3 lists in one cell, you would need to set up dependant data validation method. See this example

I'm sure that NBVC will sort your formula out

• Re: if , any nested formula

Perhaps

try
=OFFSET(A1,0,(ISNUMBER(MATCH(\$H\$4,A:A,0))*0+ISNUMBER(MATCH(\$H\$4,B:B,0))*1+ISNUMBER(MATCH(\$H\$4,C:C,0))*2),1,1)

Triumph without peril brings no glory: Just try

• Re: if , any nested formula

Or... =INDEX(\$A\$1:\$C\$1,SUMPRODUCT((\$A\$2:\$C\$20=H4)*(COLUMN(\$A\$2:\$C\$20))))

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: if , any nested formula

Quote from NBVC;688405

Or... =INDEX(\$A\$1:\$C\$1,SUMPRODUCT((\$A\$2:\$C\$20=H4)*(COLUMN(\$A\$2:\$C\$20))))

Yes, this formula works more than the above one.
Appreciated.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!