Help me fixing my excel formula...

Hi guys, as per title i need help regarding my formula. Attached excel file...

1. Input "HD50" until "HD5000" giving error "#VALUE!". Why is that happen, some others input all working fine..

for example:

A100 will show result 1A
HB300 will show 2A

Edit: Im sorry attaching wrong file...

More info on project:

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

More sample INPUT vs RESULT
y873gvs.png

UPDATE: EXPECTED FINAL RESULT

Something like this, with other data cell will be hide and lock

ZMHlTtR.png

Hi.

I don't understand your formula in F6, in particular why you are first concatenating "" and then applying -- to the result of the SUMPRODUCT. The first renders the numeric result of that function as text, the second then takes this text number and converts it back to a numeric. But why? Isn't performing these two opposite operations equivalent to performing neither?

And this explains your #VALUE! error: if the result of the IF clause is FALSE, then you have:

=--"Not Found"

and of course this results in an error, since you are attempting to perform a mathematical operation on a text string.

Regards

You don't need the helpcell if you use this formula.

c4 =INDEX($B$6:$D$18,match($C$3,$C$6:$C$18,0),3)

HD50" until "HD5000" giving an error since they are not found in the data in column C.

You ask for an exact match (you use 0) and therefore the data is not found.

(1 is the other option => not exact match).

because in row 10 you have:
HD30.29 HD58.6
and then again in row 13:
HD4901 HF5415

So in your formula in cell F6 function SUMPRODUCT returns 2 for input like HD4901, HD50, HD5000 and so on.

And then the formula takes third argument of IF and returns

Formula:

Please Login or Register  to view this content.

which is obviously error.

for excel 2007 and further you can use iferror(your fomula,"not found")

for your excel 2003 version this will not work.

As per my suggestions, remove the "" and -- and just use:

=IF(SUMPRODUCT(--(B6:B162<=C3)*(C6:C162>=C3))=1,SUMPRODUCT(--(B6:B162<=C3)*(C6:C162>=C3),ROW(B6:B162)-5),"Not Found")

Regards

But ... have you noticed my comment:

because in row 10 you have:
HD30.29 HD58.6
and then again in row 13:
HD4901 HF5415

With such data there be "Not found" for the code which is appropriate for both shelfs

to find only one you coud for instance use array formula*:

Formula:

Please Login or Register  to view this content.

*)...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Originally Posted by amein

btw. any idea why HD50 cannot be found while HD48, HD49 can..

Because your formula is not a very good one. The reason that, for example, HD50 results in "Not Found" is that your formula is asking to return values only when this part:

SUMPRODUCT(--(B6:B162<=C3)*(C6:C162>=C3))

is equal to 1.

However, for HD50, this part is equal to 2, not 1, since there are 2 rows in your data for which this statement is satisfied, rows 10 and 13, i.e.:

HD30.29|HD58.6

and:

HD4901|HF5415

This is because, for the first pair, HD30.29 is <= HD50 and HD58.6 is >= HD50.

And, also, for the second pair, HD4901 is <= HD50 and HF5415 is >= HD50.

I don't understand what you are trying to do in this sheet. Perhaps you can explain?

Regards

Nope!
With my formula HD50 is located!
See my previous 2 posts, you probably missed them.
The real problem is with your data. And you probably know GIGO rule ...

And the file for reference if you face problems with array formula

@amein

It is always good to ad to who you are replying.

Replyin to #12

I can't read the PNG, so that give me no information.

If you don't want an exact match, but the lower one, you need to use 1 instead of 1

Try the formula below, and please reply the result.

c4 =INDEX($B$6:$D$18,match($C$3,$C$6:$C$18,1),3)

Originally Posted by Kaper

And the file for reference if you face problems with array formula

Thank you Kaper for many help.. I think this problem already solved with your corrections...

thanks again.