Rjn239 -
Oct 4, 2010 at 09:14 AM - Latest reply:
TrowaD

- Posts
- 2447
- Registration date
- Sunday September 12, 2010
- Status
- Contributor
- Last seen
- November 15, 2018

Related:

- Automatically vary the contents of a cell in excel
- Excel update cell value automatically - Best answers
- Excel change cell value automatically - Forum - Excel
- To change cell value automatically ✓ - Forum - Excel
- Excel how to change to value - Forum - Excel
- Excel macro to change cell color based on value ✓ - Forum - Programming
- Excel how to change font color based on value ✓ - Forum - Excel

Best answer

TrowaD

- Posts
- 2447
- Registration date
- Sunday September 12, 2010
- Status
- Contributor
- Last seen
- November 15, 2018

Hi Rjn239,

So sheet 1 could look like this:

Henkie - 06123456789

Jopie - 06987456321

Kees - 06789654123

Now you need to place the names behind the numbers or add a extra set of names behind the numbers like:

06123456789 - Henkie

06987456321 - Jopie

06789654123 - Kees

or

Henkie - 06123456789 - Henkie

Jopie - 06987456321 - Jopie

Kees - 06789654123 - Kees

Now you have got things set up for the VLOOKUP function.

Create an extra column on sheet 2 and use VLOOKUP to retreive the name corresponding to the number.

Not exactly what you asked for, but the easiest way to get it done imo.

For your second question you could use an array formula.

At the end of sheet 2 you could enter the names again with a formula in the cell next to it which could look like this:

=SUM(IF(D2:D50="Henkie",1,0))

An array formula needs to be confirmed by hitting Ctrl+Shift+Enter.

Is this helpfull to you?

Best regards,

Trowa

So sheet 1 could look like this:

Henkie - 06123456789

Jopie - 06987456321

Kees - 06789654123

Now you need to place the names behind the numbers or add a extra set of names behind the numbers like:

06123456789 - Henkie

06987456321 - Jopie

06789654123 - Kees

or

Henkie - 06123456789 - Henkie

Jopie - 06987456321 - Jopie

Kees - 06789654123 - Kees

Now you have got things set up for the VLOOKUP function.

Create an extra column on sheet 2 and use VLOOKUP to retreive the name corresponding to the number.

Not exactly what you asked for, but the easiest way to get it done imo.

For your second question you could use an array formula.

At the end of sheet 2 you could enter the names again with a formula in the cell next to it which could look like this:

=SUM(IF(D2:D50="Henkie",1,0))

An array formula needs to be confirmed by hitting Ctrl+Shift+Enter.

Is this helpfull to you?

Best regards,

Trowa

TrowaD

- Posts
- 2447
- Registration date
- Sunday September 12, 2010
- Status
- Contributor
- Last seen
- November 15, 2018

Hi Ryan,

lookup_value = The value (phone number) you want to lookup.

table_array = The table of content containing the value.

col_index_num = The column number which contains the value (name) you want to retrieve.

range_lookup = [optional] false: find the exact value. True or empty: find the closest match.

Now you might understand why I wanted you to rearrange you data like:

06123456789 - Henkie

06987456321 - Jopie

06789654123 - Kees

or

Henkie - 06123456789 - Henkie

Jopie - 06987456321 - Jopie

Kees - 06789654123 - Kees

Since the data you want to lookup needs to be in front of the data you want to retrieve.

So on your second sheet you have a list (eg. column D) of phone numbers. Insert an empty column next to them.

Then your formula would look like this:

=VLOOKUP(D1,Sheet1!$A$1:$B$3,2,FALSE)

So D1 contains the phone number you want to look up.

Sheet1!$A$1:$B$3 contains the list of phone numbers and names.

The number 2 tells excel to retrieve the data from the second column.

FALSE: find exact match.

The $ signs makes the array absolute, which doesn't change the array when dragged down.

Hopefully this makes sense.

Feel free to ask if something is still unclear.

Best regards,

Trowa

lookup_value = The value (phone number) you want to lookup.

table_array = The table of content containing the value.

col_index_num = The column number which contains the value (name) you want to retrieve.

range_lookup = [optional] false: find the exact value. True or empty: find the closest match.

Now you might understand why I wanted you to rearrange you data like:

06123456789 - Henkie

06987456321 - Jopie

06789654123 - Kees

or

Henkie - 06123456789 - Henkie

Jopie - 06987456321 - Jopie

Kees - 06789654123 - Kees

Since the data you want to lookup needs to be in front of the data you want to retrieve.

So on your second sheet you have a list (eg. column D) of phone numbers. Insert an empty column next to them.

Then your formula would look like this:

=VLOOKUP(D1,Sheet1!$A$1:$B$3,2,FALSE)

So D1 contains the phone number you want to look up.

Sheet1!$A$1:$B$3 contains the list of phone numbers and names.

The number 2 tells excel to retrieve the data from the second column.

FALSE: find exact match.

The $ signs makes the array absolute, which doesn't change the array when dragged down.

Hopefully this makes sense.

Feel free to ask if something is still unclear.

Best regards,

Trowa

=SUM(IF(D2:D50="Henkie",1,0))

I tried this formula with my own work, when I put this formula into cell A1 for example it all works lovely, but when I drag this down to put the same formula into A2,3,4, and 5 etc the D2:D50 range increases everytime, for example:

In cell A1 the primary cell the formula is =SUM(IF(D2:D50="Henkie",1,0))

In cell A2 =SUM(IF(D3:D51="Henkie",1,0))

In cell A3 =SUM(IF(D4:D52="Henkie",1,0))

In cell A4 =SUM(IF(D5:D53="Henkie",1,0))

How would I stop the range increasing? so in cell A2,3,4,5 it would still read =SUM(IF(D2:D50="Henkie",1,0)) just by dragging it down? Thanks, John.

TrowaD

- Posts
- 2447
- Registration date
- Sunday September 12, 2010
- Status
- Contributor
- Last seen
- November 15, 2018

Hi John,

You will need to make your cell reference absolute by adding a $ symbol.

You can do this to the column and/or row by manually adding a $ symbol or after selecting the range and hitting F4 a couple of times untill the desired result is visible.

In your case you want your row to be absolute so the formula would look like:

=SUM(IF(D$2:D$50="Henkie",1,0))

Best regards,

Trowa

You will need to make your cell reference absolute by adding a $ symbol.

You can do this to the column and/or row by manually adding a $ symbol or after selecting the range and hitting F4 a couple of times untill the desired result is visible.

In your case you want your row to be absolute so the formula would look like:

=SUM(IF(D$2:D$50="Henkie",1,0))

Best regards,

Trowa

I am going to give the VLookup formula ago, but was wondering if you could give me advice on using the formula.

On sheet 1 I have set it up as follows (eg):

Henkie - 06123456789

Jopie - 06987456321

Kees - 06789654123

Now on sheet 2 I have clicked on the Vlookup formula and it pops up with a box for me to fill in (excel 2007) what would I enter in the following:

lookup_value

table_array

col_index_num

range_lookup

and the =SUM(IF(D2:D50="Henkie",1,0)) formula worked perfect thanks again :)

Ryan.