VLOOKUP function
 

Hi

I have a worksheet containing several cells with formulas like the one shown
below....

=IF($W40="","",IF($I40 = "HealthFundA",VLOOKUP($W40,Fees!$A$2:$J$88,5), ""))

W40 contains item numbers
I40 contains health fund names

The purpose of the above function is to firstly check if there is a value in
W40. If there is a value in W40 and I40 contains the text "HealthFundA" then
the function looks up the equivalent item number in a worksheet called
"Fees" and returns the $ value from the fifth column in the same row on that
worksheet.

The above formulas worked fine until I added new values to the "Fees"
worksheet and sorted them into ascending order by Item Number. I then found
that the correct values were returned in my main worksheet for the old Item
Numbers, but incorrect values were returned in the main worksheet where
there were new Item Numbers in W40.

Any suggestions on where the problem may be?

Thanks in advance
Anthony

Did you increase the size of the lookup range to cater for the new items?

--
HTH

Bob Phillips

"Anthony Dowd" wrote in message
...
Hi

I have a worksheet containing several cells with formulas like the one
shown
below....

=IF($W40="","",IF($I40 =
"HealthFundA",VLOOKUP($W40,Fees!$A$2:$J$88,5), ""))

W40 contains item numbers
I40 contains health fund names

The purpose of the above function is to firstly check if there is a value
in
W40. If there is a value in W40 and I40 contains the text "HealthFundA"
then
the function looks up the equivalent item number in a worksheet called
"Fees" and returns the $ value from the fifth column in the same row on
that
worksheet.

The above formulas worked fine until I added new values to the "Fees"
worksheet and sorted them into ascending order by Item Number. I then
found
that the correct values were returned in my main worksheet for the old
Item
Numbers, but incorrect values were returned in the main worksheet where
there were new Item Numbers in W40.

Any suggestions on where the problem may be?

Thanks in advance
Anthony

Of Course!!!! No I didn't. Thanks for that Bob.

Anthony

"Bob Phillips" wrote in message
...
Did you increase the size of the lookup range to cater for the new items?

--
HTH

Bob Phillips

"Anthony Dowd" wrote in message
...
Hi

I have a worksheet containing several cells with formulas like the one
shown
below....

=IF($W40="","",IF($I40 =
"HealthFundA",VLOOKUP($W40,Fees!$A$2:$J$88,5), ""))

W40 contains item numbers
I40 contains health fund names

The purpose of the above function is to firstly check if there is a value
in
W40. If there is a value in W40 and I40 contains the text "HealthFundA"
then
the function looks up the equivalent item number in a worksheet called
"Fees" and returns the $ value from the fifth column in the same row on
that
worksheet.

The above formulas worked fine until I added new values to the "Fees"
worksheet and sorted them into ascending order by Item Number. I then
found
that the correct values were returned in my main worksheet for the old
Item
Numbers, but incorrect values were returned in the main worksheet where
there were new Item Numbers in W40.

Any suggestions on where the problem may be?

Thanks in advance
Anthony