IF / ISNA Lookup formula

[will31]

Hi All,

I have a lookup table with two columns one like this:

Excel Workbook
	A	B
1	Yes	Yes
2	No	No
3	N_A	Not applicable
Sheet1

And have a dropdown to give the user the option to choose one of the three in column B. I have created a lookup thus;

=IF(ISNA(Index etc),"",Index etc)) to lookup the appropriate value in column A when the value from column B is selected. I wondered if a simpler method would be to try to use boolean logic to avoid the error statement if the cell was blank? If I use (X1<>"")*(Index etc) it returns an error since I guess it is looking for a numerical value.

In addition, could boolean logic return the value for the lookup?

The data from column A is being used in another program and the excel sheet will be used to upload information into this database and needs to be useable in Excel 2003.

 

[gsbelbin]

Hi All,

I have a lookup table with two columns one like this:

Excel Workbook
	A	B
1	Yes	Yes
2	No	No
3	N_A	Not applicable
Sheet1

And have a dropdown to give the user the option to choose one of the three in column B. I have created a lookup thus;

=IF(ISNA(Index etc),"",Index etc)) to lookup the appropriate value in column A when the value from column B is selected. I wondered if a simpler method would be to try to use boolean logic to avoid the error statement if the cell was blank? If I use (X1<>"")*(Index etc) it returns an error since I guess it is looking for a numerical value.

In addition, could boolean logic return the value for the lookup?

The data from column A is being used in another program and the excel sheet will be used to upload information into this database and needs to be useable in Excel 2003.

I'm not exactly clear on what you're after but you said you get an error when you use (X1<>"")*(Index etc) because it's looking for a numerical value. You can convert a boolean to a numerical by putting -- in front of it ie --(X1<>""). This will convert a TRUE to 1 and a FALSE to 0.

 

[will31]

I guess I shouldn't have written the post so late in the evening!!

I am looking for an alternative to ISNA and the lengthy formula I posted. I have tried to use (X1<>"")*(Index etc) but since the values are text and not numbers I get a #value error.

I read on Ozgrid that you can take the Lookup to another cell and use the cell location in the formula to reduce the lookup formula used to calulate the result but wonderted if it could be taken further with an alternative to ISNA.

Is this clearer?

 

[jasonb75]

If you have a working formula that you just want to make more efficient then try posting the whole formula.

 

[jasonb75]

I doubt it will be quite so simple, but from a second read of your post this looks possible.

=IF(X1,Index etc,"")