Excel LOOKUP question 2

[music1111]

I thought maybe this should be in a different post than my last one. I have two columns with 12 possible combinations (instead of 6 that I did have) and the IF/AND doesn't work anymore since you can't have more than 7 functions in the IF statement. I would like to try the LOOKUP functions as posted in my last question, but I don't quite understand the logic.

I have 2 columns: the first columns choices are 1 or 2, and the second columns choices are 1 thru 6, each combo needing a different result displayed.

Thanks,
Christine

 

[music1111]

or maybe there is a better way to get the results pulled from the data table without using the LOOKUP function?

Christine

 

[xlbo]

Based on the data you are looking up FOR (ie your combination of 1,2 and 1 - 6) in A1 and B1 and your data table having 4 columns (on a seperate sheet)
A B C D
1 1 11 1
1 2 12 2
1 3 13 3
1 4 14 etc etc
1 5 15
1 6 16
2 1 etc etc
2 2
2 3
2 4
2 5
2 6
name the range C112 as myData (Insert>Name>Define)
then use, in C1, enter
=vlookup(A1&B1,myData,2,false)
and you should get the answer you want Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[music1111]

Geoff,

Thanks! I will try this.

Christine

 

[music1111]

Geoff,

I tried this but it won't pull my value from the table. Here's what I put in the cell: =VLOOKUP(C12&D12,myData,3,FALSE) and here's my table (3 columns):
11,.080,.097
12,.069,.076
13,.016,.017
14, etc., etc.
15,
16,
17,
up to 26

PLEASE HELP! I am going crazy. This should be so simple.

Thanks,
Christine

 

[xlbo]

Try this:
=VLOOKUP(value(C12&D12),myData,3,FALSE)
If C12 & D12 are TEXT, this will correct it
or vice versa
=VLOOKUP(text(C12&D12,"0&quot,myData,3,FALSE)
if the data in "myData" is TEXT Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[music1111]

Geoff,

The VALUE function worked. Thanks! Can I ask one more question? Now my column won't add. Sometimes there are blank rows in the box and now that I changed the formula, all the cells say #NA and the cells in that same column that have a total, now won't add. Can you suggest anything for that?

Thanks for all your help,
Christine

 

[xlbo]

I'm guessing that you have numbers that are ACTUALLY text
Formatting as a number doesn't change the fact that the UNDERLYING data is text

To correct this, either use the VALUE function (as I did earlier) or use the famous "*1 fix"

To do this, enter
1
in a blank cell anywhere
Select that cell
Edit>Copy
Select all cells that you wish to convert to numbers
Edit>Paste Special
Multiply
OK

This should force all your text that looks like numbers to be recognised as numbers

Failing that, if you post your email address, I will mail you and you can reply back with your workbook (which I can then look at and try to correct) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[music1111]

Geoff,

Thank you. I would appreciate you looking to see if there is an easier way. I stuck a zero in the fields that I needed to so it would calculate. The problem with that is, I am afraid that the users countywide will take out a zero and then it won't calculate. I figured there may be a better way to do this, and I couldn't get the VALUE function to work, but I could have been putting it in the wrong cell.

you can email me at christine_terry@cfins.com

Thanks.