Enter text base on cell value/text referencing a dynamic list

[blastpwr1970]

Hi to all,

Hopefully you somebody can help me!

I am trying to enter text in a cell depending on a list of text on another sheet

Please see the attached sheet for full details and example.

Random text can grow or shrink	Output sample with formula or VBA range will grow or shrink depending on random text on the left	List text to compare on another Sheet can grow or shrink
Text1	Output1	Text1
Text2	Output1	Text2
Text3	Output2	Text3
Text9	Not working with formula	Text4
Text5	Output5	Text5
Text6	Output6	Text6

Formula get truncated:
IF((B2="Text1"),"Output1",IF((B2="Text2"),"Output1",IF((B2="Text3"),"Output 2",IF((B2="Text4"),"Output2",IF((B2="Text5"),"Output5",IF((B2="Text6"),"Out put6",IF((B2="Text7"),"Output7",IF((B2="Text8"),"Output8"))))))))


Thank you all for all of your help!
You are great

[mikerickson]

It sounds like VLOOKUP is a function that you could use.

[blastpwr1970]

I can use the VLOOKUP but the problem is that is limited to 8 lookups I then it will give me an error I have big list that I need to compare, now if you know a way to enter more that 8 please let me know. see the attached sheet for more information on what is it I am trying to do.
Thank you

[mikerickson]

You could put
=IF(ISNUMBER(MATCH(B2,Sheet2!$A:$A,0)),F2)
in E2 and drag down