find and show from list

[marthinusnel11]

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?

[#2]

@marthinusnel11 

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.

[#3]

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.

[#4]

@marthinusnel11 

Does this do what you want?

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

[#5]

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

[#6]

@marthinusnel11 

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)
)

[#7]

@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.

Thank You for your assistance.

[#8]

@marthinusnel1180 

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.

[#9]

@marthinusnel1180 

As variant

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