Excel Lookup Problem...

[AidyH]

Hi everyone,

I'm trying to get a lookup working. Basically there are several step
involved:

1. Two letters are entered into different cells, e.g. 'F' and 'A'.
2. I was trying to use a lookup to search the workbook list below
firstly finding 'FH' and then seeing if 'A' (the second letter) i
between a and h, if not checking the list 'FT' (between I and T)
otherwise 'FZ' (U and Z).
3. To then return the result, 'Beans' as 'F' and 'A' are between th
cell in the list FH.

An example of the workbook list is as follows:
fh (Letter F, between A and H) Beans
ft (Letter F, between I and T) Potatoes
fz (Letter F, between U and Z) Cabbage

I think my explanation is rubbish, sorry! Any help would be muc
appreciated. Thanks

 

[Aladin Akyurek]

How about?...

X2 houses "F", Y2 houses "A", the lookup values of interest.

=LOOKUP(Y2,INDIRECT(X2&"Table"))

FTable consists of 2 columns:

A Beans
I Potatoes
U Cabbage

Obviously, it's better to choose for a set up that not requires INDIRECT.

 

[AidyH]

Hi Aladin,

I've tried that, but it still won't work. I must be doing somethin
wrong, as all it does is come up with '#Value!" error. I've adjuste
the information in your formulae to suit my workbook, but it stil
won't work.

Any help would be appreciated

 

[Aladin Akyurek]

Did you use the suggested set up? What is the formula you actually tried?

 

[AidyH]

Thanks for replying, this is what I did:

Cell C25 = F
Cell C26 = H

The Formulae is: =LOOKUP(B7,INDIRECT(C25&"Food!A2:A43,D243")).

The reason for doing it like this is that column A2:A43 contains th
code (i.e. FT etc) and I then need it to read the fruit / vegetabl
from D243.

As you know, I need it to do the following:
1. Find the first letter, from cell C25.
2. Check to see if the info in cell C26 is between FT in colum
A2:A43.
3. As H is less than the second letter 'T', it should then display th
fruit, such as Apples etc.

It doesn't work, just stating '#REF!'.

An example of A2:A43 to show how the lookup needs to work is:
AT
AZ
BH
BP
BZ

D243 is:
Apples
Banana
Mango
Peach
Plum
Pear

The worst thing is that I can only get it working in this way.

Thanks again

 

[Aladin Akyurek]

The LOOKUP formula I suggested is dependent on the set up I suggested along
with it. If you want me to look at your spreadheet, send me a copy.