Excel Lookup Problem...

  • Thread starter Thread starter AidyH
  • Start date Start date
A

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
 
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.
 
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
 
Thanks for replying, this is what I did:

Cell C25 = F
Cell C26 = H

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

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 D2:D43.

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

D2:D43 is:
Apples
Banana
Mango
Peach
Plum
Pear

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

Thanks again
 
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.
 
Back
Top