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)

 

[Marcelo Branco]

Depending on Units first column format, either

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

Or (if number)
=VLOOKUP(0+MID(A4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A4&"0123456789")),1),Units,2,FALSE)

Hope this helps

M.

 

[Justinian]

Both return an #N/A.

Units tab is formatted as number.

 

[Marcelo Branco]

Could you provide a small data sample along with expected result(s)?

M.

 

[Justinian]

Here you go:

https://app.box.com/s/dfxsgar2yda9hjx83sqs007f8ed1nok8

 

[Justinian]

The VLOOKUP extracts the unit number on the Units tab and the MID formula extracts just the number of bedrooms in that unit.

 

[Marcelo Branco]

This is a big file. Many cannot for security reasons follow a link. Try to show us a small data sample - 5 to 10 rows suffice.

M.

 

[Justinian]

It is not easy without a file or Excel Jeanie.

VLOOKUP on B4 returns Twr2S#1 and the MID formula extracts only the number. I need to combine the formulas.

 

[Marcelo Branco]

Select a relevant part of your data (~10 rows). Copy (Ctrl+C) and paste (Ctrl+V) in the forum reply page.

M.

 

[Justinian]

[TABLE="width: 137"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]4308K[/TD]
[TD]L1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4908K[/TD]
[TD]L2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4805J[/TD]
[TD]L1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1812[/TD]
[TD]L1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]