lookup tables

[Guest]

I have returned a number to a cell from a formula. I now in another cell wish
to use a lookup statement to find this number in a list to return a value,
but this returns #N/A. Looking at the statement this is because the number
gets surrounded with Expanation marks ie " ". If I type the number manually
the lookup table works fine, how do I get around this?

 

[Nick Hodge]

We would need to see this first formula

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

The first formula is simply =(Cell Reference) ie =E1 this then just returns
the vaule from that cell, to another cell I have chosen ie E2

I then in the lookup table ask for the value in E2 to be found in a list but
it returns a #N/A but if I type the number in the lookup statement instead it
finds the number in my list and returns the correct item from the column
seclected. I have used the tool to explore the formula and it apperas that
the number in E2 gets surrounded, ie if the number was 345 the lookup table
looks for "345" thus dosent find it. I have then tried creating my list with
the numbers being "number" but the lookup table just finds the last number in
the list.

Yep I'm stuck.

 

[Guest]

Looking further into this I believe the problem to be around that the E1 cell
pulls info from another cell that has the following formula
=IF(AND(F1=28,F2=28),CONCATENATE(B5,C5,D5,E5))

The B5 - E5 being cells having single numbers, thus the Concatenate joins
the individual numbers to a four digit number. I guess this process makes the
number be seen as text thus why there are the " " marks are added.

Any ideas to get around this.

 

[Guest]

My brain has engaged again and I have finally worked it out!!!

=IF(AND(F1=28,F2=28),ABS(CONCATENATE(B5,C5,D5,E5))) I added the ABS section
to return an absolute value.

Thanks for your help.

 

[Nick Hodge]

Sorry, was a day off the board and through three posts, you sorted it
yourself...well done!

Don't know how I helped...maybe I should spend more time away

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)