New Contributor

# find and show from list

Hello, I have a drop down menu with items. I am looking for a fourmula to help with:

Once selecting an item on the drop down menu a want the next cell to do the following:

search for the same text in a list on the next sheet and then show the value next to the text found, can someone help?

8 Replies

# Re: find and show from list

You can use the VLOOKUP function for this.

Let's say the dropdown is in cell B2.

And the list is in A2:B100 on a sheet named Sheet 2.

In C2, next to the dropdown:

=IFERROR(VLOOKUP(A2, 'Sheet 2'!\$A\$2:\$B\$100, 2, FALSE), "")

This can be filled down if required.

# Re: find and show from list

Thank you hans

I am also trying to do the following if you can assist? I want a formula that does the following:
Say the value in D is equal to the list name on sheet 2 then i want to use the formula you gave before to search in that specific list, I have 3 different list i want to search and return a value from.

# Re: find and show from list

Does this do what you want?

=IFERROR(VLOOKUP(A2, INDIRECT(D2), 2, FALSE), "")

# Re: find and show from list

Not exactly! I will explain in a bit more detail:
Sheet 2 contains three lists named list1, list2 and list3. On sheet 1 I have three columns, column 1 with containing the option list1, list2 or list 3, column 2 containing a dropdown menu with brands and column 3 containing the formula a require. If for example I select list 1 in column 1 and a specific brand in column 2, then the formula should go to list1 on sheet to and search for the brand and return the value next to the brand! Hope this helps

# Re: find and show from list

As variant for such model

formula is

``````=XLOOKUP([@Brand],
INDEX(CHOOSE(([@Brands]="Brands1")+2*([@Brands]="Brands2")+3*([@Brands]="Brands3"),Brands1,Brands2,Brands3),0,1),
INDEX(CHOOSE(([@Brands]="Brands1")+2*([@Brands]="Brands2")+3*([@Brands]="Brands3"),Brands1,Brands2,Brands3),0,2)
)``````

# Re: find and show from list

@Sergei Baklan see attached.

On tracker sheet. I want column F to have the formula:

It should search for material in column C using Column B "C" as list name within sheet 3 below List C for the material and either return Yes when found or Not found.

# Re: find and show from list

In F10:

=IFERROR(VLOOKUP(C10,IFS(B10="A",'ABC MAY'!\$A\$2:\$B\$10,B10="B",'ABC MAY'!\$D\$2:\$E\$18,B10="C",'ABC MAY'!\$G\$2:\$H\$411),2,FALSE),"")

Fill down.

# Re: find and show from list

As variant

``````=IF(ISNA(XMATCH(C10,
XLOOKUP(\$B10,'ABC MAY'!\$A\$1:\$H\$1,'ABC MAY'!\$A\$2:\$H\$500)
) ),"","Yes")``````