Combining Formulas

[Justinian]

How do I combine these two formulas?

=VLOOKUP(A4,Units,2,FALSE)

=MID(A4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A4&"0123456789")),1)

 

[Justinian]

Of course. I only want to extract the number of bedrooms from each suite. In almost all the examples above, the only number in the suite is what I want to extract. In cases like T1Sig#5, I only want to extract the first number, or the number that appears prior to "S" or "sig." So if someone enters 3302G in the look up cell, the returned item would be Twr 1B and from that, I want the formula to extract the 1.

 

[Justinian]

I took a look at your file (a tedious work uff ...) and noticed that these are the possible texts returned by VLOOKUP.

[TABLE="class: grid"]
<tbody>[TR]
[TD]

Twr St​

[/TD]
[/TR]
[TR]
[TD]

Twr 1B​

[/TD]
[/TR]
[TR]
[TD]

Twr 2Bd​

[/TD]
[/TR]
[TR]
[TD]

Twr 2 U​

[/TD]
[/TR]
[TR]
[TD]

Twr2 GK​

[/TD]
[/TR]
[TR]
[TD]

G 1B L​

[/TD]
[/TR]
[TR]
[TD]

G 1BLU​

[/TD]
[/TR]
[TR]
[TD]

G 1LKU​

[/TD]
[/TR]
[TR]
[TD]

G 1U U​

[/TD]
[/TR]
[TR]
[TD]

G 2B​

[/TD]
[/TR]
[TR]
[TD]

Twr2S#1​

[/TD]
[/TR]
[TR]
[TD]

T1Sig#5​

[/TD]
[/TR]
</tbody>[/TABLE]


Could you, please, tell us the expected result for each of these cases.

M.

The results would be:

Ignore the Twr St.
1
2
2
2
1
1
1
1
2
2
1

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Text​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Twr St​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Twr 1B​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Twr 2Bd​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Twr 2 U​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Twr2 GK​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

G 1B L​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

G 1BLU​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

G 1LKU​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

G 1U U​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

G 2B​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Twr2S#1​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

T1Sig#5​

[/td][td]

1​

[/td][/tr]
[/table]

1st Step
Format B2:B13 as number with 0 decimal places

Then
Array Formula in B2 copied down
=NPV(-0.9,IFERROR(MID(LEFT(A2,LEN(A2)-1),LEN(LEFT(A2,LEN(A2)-1))-ROW(INDIRECT("1:"&LEN(LEFT(A2,LEN(A2)-1))))+1,1)/10,""))

confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Justinian]

I am confused as to why there is no VLOOKUP and why there is net present value in a look up array? I tested your formula on 4308K and got $4,308.00.

 

[Marcelo Branco]

Disregard my post above. A much simpler formula

B2
=MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),1)

M.

 

[Justinian]

Yes, that is my MID formula but my original question was how to combine that with my extant VLOOKUP.

 

[Marcelo Branco]

Yes, that is my MID formula but my original question was how to combine that with my extant VLOOKUP.

All you have to do is to substitute every instance of A2 (or A4 in post 1) by the VLOOKUP formula, that is
=MID(VLOOKUP(A4,Units,2,FALSE),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},VLOOKUP(A4,Units,2,FALSE)&"0123456789")),1)

M.

 

[Justinian]

That is perfect! Thank you so much for your time, I appreciate it!

 

[Marcelo Branco]

That is perfect! Thank you so much for your time, I appreciate it!

You are welcome. Glad to help.

Just an advice. Next time try to be specific. If we knew exactly what you wanted it would not take so much time and so many replies to find the solution to your problem. Just saying that you wanted to combine two formulas is somewhat vague.

M.