Two formulas one cell??

[Tartesos]

Hello again,

I need to use two lookup formulas in one cell. One will look on the 9th number or letter and depending on the data in that place will give an output. The other formula will look the 8th and the 9th number and depending on that combination will give the correct output data. How can I do this??

This is one formula that will look for the 9th:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4,5,6,7,8,9},"",
VLOOKUP(MID(B5,9,1)+0,configuration!$L$3:$M$31,2,0),
VLOOKUP(MID(B5,9,1),configuration!$L$3:$M$31,2,0)))

This the formula that will look for the 8th and 9th:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4,5,6,7,8,9},"",
VLOOKUP(MID(B5,8,2)+0,configuration!$L$3:$M$31,2,0),
VLOOKUP(MID(B5,8,2),configuration!$L$3:$M$31,2,0)))

There is any way I can get them on the same cell so if on the 9th number is found a number of letter will give an output and if it find a number or letter on the 8th and 9th will give other output?
On the L column you can find single numbers or letters and also a combination of two numbers or two letters. The problem is that now I can only use one of this formulas and that means that I can't cover the results I want, any help ??

Regards,

 

[CWatts]

You can join them with the & operator so your cell will display the result of the first output and the result of the second output.

So say the result of the first formula is A and the second formula is GA, your output would be AGA or you could use &"-"& between the formulas and your output would be A-GA and so forth. Does that do what you need?

 

[mmyett]

I must be missing something, but why wouldn't you just concatenate the 2 formulas into 1 with a space or return or whatever you need inbetween?

 

[Tartesos]

The problem here is that I need a formula that if it finds on the 8th and 9th place of a number this: 01, 11, 21, 41, 51, 61, 71, 81 and 91 it will give the same output data but if there is not that combination between 8th and 9th it need to look on the 9th place and give the correct data depending on that 9th value.

So lets say that the 9th number is 2 and the 8th 1 so 12. Then the formula should give me the output for 2.
If the formula finds on the 8th and 9th any of the combinations ( 01, 11, 21, 41, 51, 61, 71, 81 and 91) it should give the same output.
I think that the formula I use now is not going to do that. Can anyone help me with this ??

Regards,

Can this be done with the IF statement ??? If yes... can anyone help me with this??

 

[CWatts]

Try this:

=IFERROR(INDEX({"01","11","21","41","51","61","71","81","91"},MATCH(MID(A2,8,2),{"01","11","21","41","51","61","71","81","91"},0)),MID(A2,9,1))

I'd suggest using a named range instead of the {} in the cell to clean things up a bit but that should do it.

Edit, if you aren't using Excel 2007 or above it wont work, so if it fails, let us know the version of excel you're using.

 

[Tartesos]

Umm, ok, but I have the data in column "L" where I have the 9th number and the 8th and 9th combination. In the "M" column I have the output data.
How do I get this formula to get the information from those columns??

Regards,

 

[Tartesos]

Anyone can help me ??

 

[Tartesos]

When I use the formula like this:
=IFERROR(INDEX({"01","11","21","41","51","61","71","81","91"},MATCH(MID(B1071,8,2),{"01","11","21","41","51","61","71","81","91"},0)),MID(B1071,9,1))

I just get a "71"as output data ... how can I redirect this formula to takethe info from the L and M columns?

Regards

 

[jasonb75]

If both formula work, why not just merge them?

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4,5,6,7,8,9},"",
VLOOKUP(MID(B5,8,2)+0,configuration!$L$3:$M$31,2,0),
VLOOKUP(MID(B5,8,2),configuration!$L$3:$M$31,2,0),
VLOOKUP(MID(B5,9,1)+0,configuration!$L$3:$M$31,2,0),
VLOOKUP(MID(B5,9,1),configuration!$L$3:$M$31,2,0)))

Your explaination doesn't make a great deal of sense.

 

[Tartesos]

If both formula work, why not just merge them?

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4,5,6,7,8,9},"",
VLOOKUP(MID(B5,8,2)+0,configuration!$L$3:$M$31,2,0),
VLOOKUP(MID(B5,8,2),configuration!$L$3:$M$31,2,0),
VLOOKUP(MID(B5,9,1)+0,configuration!$L$3:$M$31,2,0),
VLOOKUP(MID(B5,9,1),configuration!$L$3:$M$31,2,0)))

Your explaination doesn't make a great deal of sense.

Let me try this one.