Not a trick question, what is wrong with this formula

[bmpreston]

So I have a column of formulas. I clicked and dragged to autofill the formulas and at some random point, it stopped working.

Can anyone see a difference?

=IF(A123=0,0,VLOOKUP(EQUIPMENT!A123,EQUIPMENT!$L$3:$P$2888,5)*B123)


This is the last one that worked

=IF(A122=0,0,VLOOKUP(EQUIPMENT!A122,EQUIPMENT!$L$3:$P$2888,5)*B122)

It seems as if something on the EQUIPMENT page is different. But nothing seemingly changes.

Thanks

 

[Michael M]

Do you get an error ?
What is in A123 and EQUIPMENT!A123 and B123 ?
Do they compare to what is in the previous row / s ?

 

[Fazza]

Maybe the formula is perfect?
Maybe the data is the issue?
Maybe more info is needed?

 

[bmpreston]

So this is a grab without grids, but that's A120, B, C, D

A is a formula, it just points back to EQUIPMENT!A*

[TABLE="width: 835"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]100582626[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]FLEXIDOME IP MICRO 5000[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD="align: right"]100584567[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]PTZ CONTROL NO VIDEO SCREEN[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD="align: right"]100586849[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]CAMERA FLEXIDOME 960H D/N WDR (REPLACES VDN-498V03-21S) [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD="align: right"]100587323[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]CABLE CAT 5E JUMPER 3FT YELLOW[/TD]
[TD] $ -

[/TD]
[/TR]
</tbody>[/TABLE]


Basically, everything on this page is a VLOOKUP back to the EQUIPMENT tab, and on the EQUIPMENT tab, it's just a column of pricing. I'm so confused why one works and then some don't.

Thanks

 

[JoeMo]

You didn't say what "stopped working means". Meanwhile, is EQUIPMENT!$L$3:$L$2888 sorted to be in ascending order? If not you can expect some strange results b/c you have taken the default value of the range_lookup rather than asking for an exact match.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

 

[bmpreston]

That is really interesting about the being sorted. I never knew that all the numbers in a lookup must be in numerical order. It is to my best knowledge Ascending order. Regarding the results, I just get a 0, since my IF statement is designed to give a zero if the A cell is empty or null.

Interestingly if I process the Evaluate Formula, it sees the first IF function as false, as it should. A123 IS NOT 0, then it processes the VLOOKUP, but still gives me a zero.

 

[JoeMo]

That is really interesting about the being sorted. I never knew that all the numbers in a lookup must be in numerical order. It is to my best knowledge Ascending order. Regarding the results, I just get a 0, since my IF statement is designed to give a zero if the A cell is empty or null.

Interestingly if I process the Evaluate Formula, it sees the first IF function as false, as it should. A123 IS NOT 0, then it processes the VLOOKUP, but still gives me a zero.

If you are uncertain, and are actually looking for an exact match to you're lookup value, try it this way:

=IF(A123=0,0,VLOOKUP(EQUIPMENT!A123,EQUIPMENT!$L$3:$P$2888,5,0)*B123)

The red 0 tells Excel you are looking for an exact match (in which case the first column of values in the lookup table do not have to be sorted in any way). If you leave that argument out (as your OP does), then it defaults to 1 which requires the ascending values.

 

[bmpreston]

If you are uncertain, and are actually looking for an exact match to you're lookup value, try it this way:

=IF(A123=0,0,VLOOKUP(EQUIPMENT!A123,EQUIPMENT!$L$3:$P$2888,5,0)*B123)

The red 0 tells Excel you are looking for an exact match (in which case the first column of values in the lookup table do not have to be sorted in any way). If you leave that argument out (as your OP does), then it defaults to 1 which requires the ascending values.

That works! Amazing. Never knew about exact matching.

Thanks!

 

[JoeMo]

That works! Amazing. Never knew about exact matching.

Thanks!

You are welcome - thanks for the reply.