Form with LOOKUP

[pansovic]

Let me start to say that I understand the Lookup formula though I have the following issue: let say I have (I hope I use this code correctly)

Please Login or Register  to view this content.

Now I want to create a form where I type for bananas and result should be on first line the green bananas and second line yellow bananas. So, how can I create a Lookup formula where it takes the second line and not again the first line

[NBVC]

Say your list starts in Sheet1!A2 and you enter "Bananas" in Sheet2, A1

in Sheet1!D2 (next available column) enter:

=Countif(A$1:A1,A1) and copy down

in E2: =Max(D:D)

Then in Sheet2!A2,

=IF(ROWS($A$1:$A1)>Sheet1!$E$2,"",INDEX(Sheet1!B:B,MATCH(ROWS($A$1:$A1),Sheet1!$D:$D,0)))

copy it down.. and if you want to next column to get the numbers too.

[pansovic]

When I type in Sheet2 A1 Oranges nothing is changing. When I leave it blank also nothing is changing. I keep to have in Sheet2 B always the color of bananas.

[NBVC]

Sorry, my Countif Formula should have been referring to Sheet2!$A$1

=COUNTIF(A$1:A1,Sheet2!$A$1)

see attached..

[pansovic]

Thanks but it leads me to another question. I need the data for different forms so in the below case I will need it for Sheet2 but as well for Sheet3 and 4. How can I add different criterias in COUNTIF?

[pansovic]

Dear Forum Administrator,
This thread is solved though it doesn't give me the option to mark SOLVED. Can you maybe make this SOLVED?