Is there a way to build/use hash tables in Excel ?

E.g.
My table contains

... 2.
.... ...
value_n 2.5
value_n+1 2.6
value_n_2 3.4
....
....

I am pretty sure the stuff on the left column occurs only once, but I
don't know what row value_n (or others for that matter) exist, and
their order changes every time I import my data from elsewhere.

Instead of using Control + F to find these and then find their values
(since I have a lot of these), I was wondering if there's a way to
build a hash table so I can use a formula like... $MYHASH{value_n} to
get either the value 2.6, or the row/col address so I can work
something out.

Can we do this?

Mahurshi Akilla

Yes, it is possible to create and use hash tables in Excel. One way to do this is by using the VLOOKUP function in combination with a helper column.

Here are the steps to create a hash table in Excel:

1. Create a new column next to your data table. This will be your helper column.
2. In the first cell of the helper column, enter the formula =HASHCELL(A2), where A2 is the cell containing the first value in your data table.
3. Press Enter to apply the formula to the first cell of the helper column.
4. Double-click the fill handle (the small square in the bottom right corner of the cell) to apply the formula to the rest of the cells in the helper column.
5. In a new cell, enter the formula =VLOOKUP("value_n",B:C,2,FALSE), where "value_n" is the value you want to look up, and B:C are the columns containing your helper column and the corresponding values.
6. Press Enter to apply the formula and get the corresponding value for "value_n".

Here's how the formula in step 2 works:

=HASHCELL(A2)

This formula uses the built-in Excel function HASHCELL to generate a unique hash value for the value in cell A2. The hash value is a numeric representation of the value that can be used as a lookup key.

You can create a custom function in VBA to use the HASHCELL function. Here's an example of what the VBA code might look like:

This function takes a value as input and returns a hash value as output. The hash value is calculated by adding up the ASCII codes of each character in the value.

Once you have the hash table set up, you can use the VLOOKUP function to look up values based on their hash values. This can be much faster than using the FIND function or other methods to search for values in a large data set.
[/list]

Fortunately your function already exists in Excel. It is call VLOOKUP:

Say in A1 thru B8 we have:

100 4.927273878
23 2.870022374
53 8.603009807
75 2.272587939
97 1.199421135
200 1.628241313
1 3.071201624
7 4.384416696

in another cell enter:
=VLOOKUP(75,A1:B8,2)
to display:
2.272587939


Please note that the data in cols A & B does not needed to be sorted.

--
Gary''s Student - gsnu200743


"Mahurshi Akilla" wrote:

Is there a way to build/use hash tables in Excel ?

E.g.
My table contains

... 2.
.... ...
value_n 2.5
value_n+1 2.6
value_n_2 3.4
....
....

I am pretty sure the stuff on the left column occurs only once, but I
don't know what row value_n (or others for that matter) exist, and
their order changes every time I import my data from elsewhere.

Instead of using Control + F to find these and then find their values
(since I have a lot of these), I was wondering if there's a way to
build a hash table so I can use a formula like... $MYHASH{value_n} to
get either the value 2.6, or the row/col address so I can work
something out.

Can we do this?

Mahurshi Akilla

Please note that the data in cols A & B does not needed to be sorted.

It does if you leave out the fourth argument for VLookup
=VLOOKUP(75,A1:B8,2,0)


"Gary''s Student" wrote:

Fortunately your function already exists in Excel. It is call VLOOKUP:

Say in A1 thru B8 we have:

100 4.927273878
23 2.870022374
53 8.603009807
75 2.272587939
97 1.199421135
200 1.628241313
1 3.071201624
7 4.384416696

in another cell enter:
=VLOOKUP(75,A1:B8,2)
to display:
2.272587939


Please note that the data in cols A & B does not needed to be sorted.

--
Gary''s Student - gsnu200743


"Mahurshi Akilla" wrote:

Is there a way to build/use hash tables in Excel ?

E.g.
My table contains

... 2.
.... ...
value_n 2.5
value_n+1 2.6
value_n_2 3.4
....
....

I am pretty sure the stuff on the left column occurs only once, but I
don't know what row value_n (or others for that matter) exist, and
their order changes every time I import my data from elsewhere.

Instead of using Control + F to find these and then find their values
(since I have a lot of these), I was wondering if there's a way to
build a hash table so I can use a formula like... $MYHASH{value_n} to
get either the value 2.6, or the row/col address so I can work
something out.

Can we do this?

Mahurshi Akilla

Thanks for the clarification
--
Gary''s Student - gsnu200743


"JMB" wrote:

Please note that the data in cols A & B does not needed to be sorted.

It does if you leave out the fourth argument for VLookup
=VLOOKUP(75,A1:B8,2,0)


"Gary''s Student" wrote:

Fortunately your function already exists in Excel. It is call VLOOKUP:

Say in A1 thru B8 we have:

100 4.927273878
23 2.870022374
53 8.603009807
75 2.272587939
97 1.199421135
200 1.628241313
1 3.071201624
7 4.384416696

in another cell enter:
=VLOOKUP(75,A1:B8,2)
to display:
2.272587939


Please note that the data in cols A & B does not needed to be sorted.

--
Gary''s Student - gsnu200743


"Mahurshi Akilla" wrote:

Is there a way to build/use hash tables in Excel ?

E.g.
My table contains

... 2.
.... ...
value_n 2.5
value_n+1 2.6
value_n_2 3.4
....
....

I am pretty sure the stuff on the left column occurs only once, but I
don't know what row value_n (or others for that matter) exist, and
their order changes every time I import my data from elsewhere.

Instead of using Control + F to find these and then find their values
(since I have a lot of these), I was wondering if there's a way to
build a hash table so I can use a formula like... $MYHASH{value_n} to
get either the value 2.6, or the row/col address so I can work
something out.

Can we do this?

Mahurshi Akilla