CODEWITHSUNDEEP
excelexcel-formula
WoShiProDevils
WoShiProDevils

Reputation: 47

Excel calculation return wrong value

I'm building a simple POS to calculate item cost. I'm able to calculate item cost for Belts and Scarf but when i calculate item cost for jewellery it return the wrong calculating.

The formula i'm using = B37 * VLOOKUP(B36, A2:B5, 2, TRUE)

Excel Picture

Upvotes: 0

Views: 115

Answers (2)

vladatr
vladatr

Reputation: 626

Your items aren't sorted. You must sort items (A3:A5) A to Z.

Changing TRUE to FALSE changes the way VLOOKUP works:

If TRUE or 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. The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. For more information, see Sort data.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Upvotes: 4

Michi
Michi

Reputation: 5471

Change the "TRUE" in your formula to "FALSE" and it should work.

= B37 * VLOOKUP(B36,A2:B5,2,FALSE)

Upvotes: 2

Related Questions