JavaScript SQL database for browser and Node.js.
Handles both traditional relational tables and nested JSON data (NoSQL).
Export, store, and import data from localStorage, IndexedDB, or Excel.

Suppose you want to find out how many times particular text or a number value occurs in a range of cells. For example:

If a range, such as A2:D20, contains the number values 5, 6, 7, and 6, the number 6 occurs two times.

If a column contains "Buchanan", "Dodsworth", "Dodsworth", and "Dodsworth", "Dodsworth" occurs three times.

Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number.

In its simplest form, the VLOOKUP function says:

=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).

There is something called 'locked reference' in excel which you can use for this, and you use $ symbols to lock a range. For your example, you would use:

=IF(B4<>"",B4/B$1,"")

This locks the 1 in B1 so that when you copy it to rows below, 1 will remain the same.

If you use $B$1, the range will not change when you copy it down a row or across a column.