Hello, I'm using Excel 2003 and need help on an If statement or not sure
whether I should use a Vlook Up table.

I have 2 spreadsheets. The first has a list of housing benefit codes e.g.
HB0001 in column A. The next column along has the nominal values of what
these codes represent e.g. £17.50. In the 2nd spreadsheet I then manually
enter each code that the recipient is entitled to in a column. I want to
create a IF statement on another spreadsheet that will automatically fill in
the nominal value that the code represents. FOr example, I want Excel to
look at the column that has all the codes that I enter and then place the
nominal value in it's place.

Example:
Codes: Nominal Value:
HB0001 £17.50
HB0002 £21.50
HB0003 £24.50
--
Kind regards
Mike

Hi Mike,

I would use a VLOOKUP along the lines of...

Say your housing codes and values are in Sheet 1 cell range A1:B100

In sheet 2 you are entering manually entering the housing codes in cell A1
and you wan the value to be in cell B1..

In sheet 2 Cell B1 type =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0)

And copy down as far as you need.

Now when you enter the Housing code in A the value will be in B

Hope this helps,

Gav.


"Mike" wrote:

Hello, I'm using Excel 2003 and need help on an If statement or not sure
whether I should use a Vlook Up table.

I have 2 spreadsheets. The first has a list of housing benefit codes e.g.
HB0001 in column A. The next column along has the nominal values of what
these codes represent e.g. £17.50. In the 2nd spreadsheet I then manually
enter each code that the recipient is entitled to in a column. I want to
create a IF statement on another spreadsheet that will automatically fill in
the nominal value that the code represents. FOr example, I want Excel to
look at the column that has all the codes that I enter and then place the
nominal value in it's place.

Example:
Codes: Nominal Value:
HB0001 £17.50
HB0002 £21.50
HB0003 £24.50
--
Kind regards
Mike

I would use your table in Sheet1 as aVLOOKUP() table. With your example
data in Sheet1 A1:B4, (labels in Row 1), and a Husing Cose entered in say C8
of Sheet2 then in D8 enter the formula:

=VLOOKUP(C8,Sheet1!A2:B4,2,FALSE)

to have the correct amount for that code returned.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Mike" wrote in message
...
Hello, I'm using Excel 2003 and need help on an If statement or not sure
whether I should use a Vlook Up table.

I have 2 spreadsheets. The first has a list of housing benefit codes e.g.
HB0001 in column A. The next column along has the nominal values of what
these codes represent e.g. £17.50. In the 2nd spreadsheet I then
manually
enter each code that the recipient is entitled to in a column. I want
to
create a IF statement on another spreadsheet that will automatically fill
in
the nominal value that the code represents. FOr example, I want Excel to
look at the column that has all the codes that I enter and then place the
nominal value in it's place.

Example:
Codes: Nominal Value:
HB0001 £17.50
HB0002 £21.50
HB0003 £24.50
--
Kind regards
Mike

Hi,

Let's assume you codes and notional values are in Book1 Sheet1 in the range
A1:B4

In book2 cellA1 you enter HB001 and you want to return the value associated
to that in another cell (say) B1. Try this in B1 of book 2

=VLOOKUP(A1,[Book1]Sheet1!$A$1:$B$4,2,FALSE)


Mike

"Mike" wrote:

Hello, I'm using Excel 2003 and need help on an If statement or not sure
whether I should use a Vlook Up table.

I have 2 spreadsheets. The first has a list of housing benefit codes e.g.
HB0001 in column A. The next column along has the nominal values of what
these codes represent e.g. £17.50. In the 2nd spreadsheet I then manually
enter each code that the recipient is entitled to in a column. I want to
create a IF statement on another spreadsheet that will automatically fill in
the nominal value that the code represents. FOr example, I want Excel to
look at the column that has all the codes that I enter and then place the
nominal value in it's place.

Example:
Codes: Nominal Value:
HB0001 £17.50
HB0002 £21.50
HB0003 £24.50
--
Kind regards
Mike

Thanks Gav, I've used as you suggested and made the cells in the table array
as Absolute cells and it worked for the first cell (B1) . However, when I use
the Fill Handler to copy the formula down it just repeats the cell in B1.
Does Autofil work within V-Lookups? What am I doing wrong?
--
Regards
Mike


"Gav123" wrote:

Hi Mike,

I would use a VLOOKUP along the lines of...

Say your housing codes and values are in Sheet 1 cell range A1:B100

In sheet 2 you are entering manually entering the housing codes in cell A1
and you wan the value to be in cell B1..

In sheet 2 Cell B1 type =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0)

And copy down as far as you need.

Now when you enter the Housing code in A the value will be in B

Hope this helps,

Gav.


"Mike" wrote:

Hello, I'm using Excel 2003 and need help on an If statement or not sure
whether I should use a Vlook Up table.

I have 2 spreadsheets. The first has a list of housing benefit codes e.g.
HB0001 in column A. The next column along has the nominal values of what
these codes represent e.g. £17.50. In the 2nd spreadsheet I then manually
enter each code that the recipient is entitled to in a column. I want to
create a IF statement on another spreadsheet that will automatically fill in
the nominal value that the code represents. FOr example, I want Excel to
look at the column that has all the codes that I enter and then place the
nominal value in it's place.

Example:
Codes: Nominal Value:
HB0001 £17.50
HB0002 £21.50
HB0003 £24.50
--
Kind regards
Mike

Hi Mike,

I've just tried it and it seemed to work for me....

=VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0)

Are you sure you haven't made the A1 after the bracket an Absolute reference??

Gav.

"Mike" wrote:

Thanks Gav, I've used as you suggested and made the cells in the table array
as Absolute cells and it worked for the first cell (B1) . However, when I use
the Fill Handler to copy the formula down it just repeats the cell in B1.
Does Autofil work within V-Lookups? What am I doing wrong?
--
Regards
Mike


"Gav123" wrote:

Hi Mike,

I would use a VLOOKUP along the lines of...

Say your housing codes and values are in Sheet 1 cell range A1:B100

In sheet 2 you are entering manually entering the housing codes in cell A1
and you wan the value to be in cell B1..

In sheet 2 Cell B1 type =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0)

And copy down as far as you need.

Now when you enter the Housing code in A the value will be in B

Hope this helps,

Gav.


"Mike" wrote:

Hello, I'm using Excel 2003 and need help on an If statement or not sure
whether I should use a Vlook Up table.

I have 2 spreadsheets. The first has a list of housing benefit codes e.g.
HB0001 in column A. The next column along has the nominal values of what
these codes represent e.g. £17.50. In the 2nd spreadsheet I then manually
enter each code that the recipient is entitled to in a column. I want to
create a IF statement on another spreadsheet that will automatically fill in
the nominal value that the code represents. FOr example, I want Excel to
look at the column that has all the codes that I enter and then place the
nominal value in it's place.

Example:
Codes: Nominal Value:
HB0001 £17.50
HB0002 £21.50
HB0003 £24.50
--
Kind regards
Mike