If this, show this

[amein]

A	B
1	1A
2	1B
3	2A
4	2B
5	2A

from table, I want result like this
eg:
If C1=1, D1 show 1A
If C1=3, D1 show 2A

[FDibbins]

Maybe this?

=IF(C1=1,1A,IF(C1=3,2A,""))

Although I have a feeling your actual situation is more complex that this. Can you share exactlt what you are trying to do?

[amein]

What if, C1=4?

this is the problem from previous thread which getting no solution, thus i try another option...

Maybe this?

=IF(C1=1,1A,IF(C1=3,2A,""))

Although I have a feeling your actual situation is more complex that this. Can you share exactlt what you are trying to do?

How about his formula? can u help me how to fix bold part so that formula read input from B column?

=IF(C1>=1,"get result from B1:B5",IF(C1 = 0, "Not Found"))

[FDibbins]

Your question was...

If C1=1, D1 show 1A
If C1=3, D1 show 2A

Thats what I answered. I also said

Although I have a feeling your actual situation is more complex that this. Can you share exactlt what you are trying to do?

So, save us both some time, and tell me exactly what you are trying to do

(ald also, please take note of teh forum rules regarding posting duplicate threads)

[amein]

Attach excel. Sorry..

7KkPGvT.png

Desired result

E3=1, C4=1A
E3=2, C4=1B
E3=3, C4=2A
E3=4, C4=2B
E3=5, C4=3A
and so on

Your question was...


Thats what I answered. I also said


So, save us both some time, and tell me exactly what you are trying to do

(ald also, please take note of teh forum rules regarding posting duplicate threads)

[FDibbins]

Still a bit confusing as to what you are trying to do there, but perhaps this?

make up a small table with your values and your results...

	I	J
8	1	1A
9	2	1B
10	3	2A
11	4	2B
12	5	3A

Then use this to find what you want...
=VLOOKUP($E$3,I8:J12,2,0)

Note though, that the "2" in E3 is actually text, not a value. Any time you concat numbers, the result is text. To overcome that, change the formula in E2 to this...
=--IF(SUMPRODUCT(--(B6:B15<=C3)*(C6:C15>=C3))=1,""&SUMPRODUCT(--(B6:B15<=C3)*(C6:C15>=C3),ROW(D6:D15)-5),"Not Found")
Note the -- after =

That formula actually doesnt really make sense though, because 1 text will never be >= another text, it will only ever be = or not =. exactly what are you trying to achieve there?

Also, you have a circular reference error in C4. the formula you have there is referencing itself...
C4=IF(SUMPRODUCT(--(B6:B15<=C3)*(C6:C15>=C3))=1,"Row no. "&INDEX(D6:D15,C4),"Not Found")

[amein]

Thank you for your help FDibbins.

Actually this project related to the library books call number. I want to create something so that user will easier search books belong to which shelf.

Column B and C is the Call Number (book reference number) and Column D represent Shelf Label.

Those inside excel only few database, i have alot more.. I know got some error... I'll try fix that later on..

1 shelf in the library have 2 side (1A and 1B), figure below..each side we label using call number (eg: G154 - HB171.5)

sNRPek7.jpg

So, let say user want to search book call number HB100, then they just input HB100, result will show this book shelve in which shelf.

Xn8xeF7.png

Still a bit confusing as to what you are trying to do there, but perhaps this?

make up a small table with your values and your results...

	I	J
8	1	1A
9	2	1B
10	3	2A
11	4	2B
12	5	3A

Then use this to find what you want...
=VLOOKUP($E$3,I8:J12,2,0)

Note though, that the "2" in E3 is actually text, not a value. Any time you concat numbers, the result is text. To overcome that, change the formula in E2 to this...
=--IF(SUMPRODUCT(--(B6:B15<=C3)*(C6:C15>=C3))=1,""&SUMPRODUCT(--(B6:B15<=C3)*(C6:C15>=C3),ROW(D6:D15)-5),"Not Found")
Note the -- after =

That formula actually doesnt really make sense though, because 1 text will never be >= another text, it will only ever be = or not =. exactly what are you trying to achieve there?

Also, you have a circular reference error in C4. the formula you have there is referencing itself...
C4=IF(SUMPRODUCT(--(B6:B15<=C3)*(C6:C15>=C3))=1,"Row no. "&INDEX(D6:D15,C4),"Not Found")

[amein]

Improve excel file after done correction with @FDibbins help