Offset match search for unsorted data

hmmm ok, see if this will get you wnat you want? (I have a feeling I have overlooked something though)
=INDEX('Paste Availability Here'!J:J,MATCH('Community Info'!$F$3&"*",'Paste Availability Here'!B:B,0)-1)

What answer where you expecting? 5/31/2015?

You left out a 0 (see bolded)...
=OFFSET('Paste Availability Here'!$B$1:$B$1000,MATCH(('Community Info'!$F$3 & "*"),'Paste Availability Here'!$B$1:$B$1000,0)-2, 8,1,1)

Im sure there is a better way to do this, give me a few minutes to look

Slight modification as I am understanding your file better...
=INDEX('Paste Availability Here'!J:J,MATCH(--MID(B4,FIND(",",B4,1)+1,5)&"*",'Paste Availability Here'!B:B,0)-1)

The bolded part will work for every1 "1st row" of each type you copy it to (D6, D12, D18 etc), but you will have to adjust the reference when you copy it within a type (D6 to D7, to D8 etc)

Originally Posted by KampKounslr

........ the problem that I am running into is that the current formula will find the first result of that floorplan but then I need the second field to find a different apartment of the same floorplan. Not sure how to get it to differentiate the results without the data already being sorted.

Have an approach which will yield this array of matched rows for Community Info $F$3 (524 Sq Ft) against floor plans in column B of Paste Availability Here. There are six of them.

{29;32;73;76;79;82;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

Designating which of the 524 sq ft floor plans you want can be done with SMALL. Here's the base formula for that.

Formula:

=IFERROR(SMALL(IF(IFERROR(MATCH(--TRIM(LEFT('Paste Availability Here'!$B$1:$B$200,3)),'Community Info'!$F$3,0),0),ROW($1:$200)),ROW($1:$200))-1,"")

To locate say the 3rd one on the list:

Formula:

=INDEX('Paste Availability Here'!$A$1:$A$200,SMALL(IFERROR(SMALL(IF(IFERROR(MATCH(--TRIM(LEFT('Paste Availability Here'!$B$1:$B$200,3)),'Community Info'!$F$3,0),0),ROW($1:$200)),ROW($1:$200))-1,""),3))

To locate price with same:

Formula:

=INDEX('Paste Availability Here'!$E$1:$E$200,SMALL(IFERROR(SMALL(IF(IFERROR(MATCH(--TRIM(LEFT('Paste Availability Here'!$B$1:$B$200,3)),'Community Info'!$F$3,0),0),ROW($1:$200)),ROW($1:$200))-1,""),3))+'Community Info'!$K$3

To locate available date:

Formula:

=INDEX('Paste Availability Here'!$J$1:$J$200,SMALL(IFERROR(SMALL(IF(IFERROR(MATCH(--TRIM(LEFT('Paste Availability Here'!$B$1:$B$200,3)),'Community Info'!$F$3,0),0),ROW($1:$200)),ROW($1:$200))-1,""),3))

These are all array entered....committed by holding Ctrl + Shift while hitting Enter. I put these in row 8 under "Saguaro: 1x1, 524 Sq. Ft., W/D included" section to coincide with the 3rd matching item in column B of Paste Availability Here.

File is attached.

Doe this help? If yes will need to modify MATCH criteria. This one forces the 3 left most characters of Sq ft. Will tweak to complete this part if this much does the job so far.

The "--" part coerces text values presented as numbers into their underlying numeric values. Since 524 is already a number in the Community Info sheet it can only match with the same number. The LEFT function removes the 3 left most characters from the located item in Paste Availability Here, but LEFT is a text function. The resulting number needs to be coerced into value 524 or else it is evaluated as 0. 0 is also a number just not what you want.

I'm glad this looks promising. However these formulas aren't flexible. They could stand some "tuning".

1.) LEFT extracts the first 3 left most characters described above. Not all Sq ft is 3 digits. That can be fixed.

2.) Designation of the 3rd available 524 sq ft unit is hard coded. That could stand a helper cell.

3.) The manner in which ROW is assigned is not the standard way. It works here because of serendipity. Everything here seems designed to start in row 1. I went with that. Wiser approach is in order should you insert new rows into the table. The ranges / row stop at row 200. If you add beyond that those will need to change to accommodate the new range. I / we strongly advise people to resist referencing whole columns / rows in their formulas. It consumes resources and slows workbooks down.

I've been working on some of the details of those things pending your feedback.

Here's the fix for the Sq ft extractions. I arbitrarily picked cell O2 In-House Price sheet to input the 1,2,3,...... replacing the hard coded number 3. For the unit number:

Formula:

=INDEX('Paste Availability Here'!$A$1:$A$200,SMALL(IFERROR(SMALL(IF(IFERROR(MATCH(--TRIM(LEFT('Paste Availability Here'!$B$1:$B$200,SEARCH(" Sq",'Paste Availability Here'!$B$1:$B$200)-1)),'Community Info'!$F$3,0),0),ROW($1:$200)),ROW($1:$200))-1,""),$O$2))

For the price:

Formula:

=INDEX('Paste Availability Here'!$E$1:$E$200,SMALL(IFERROR(SMALL(IF(IFERROR(MATCH(--TRIM(LEFT('Paste Availability Here'!$B$1:$B$200,SEARCH(" Sq",'Paste Availability Here'!$B$1:$B$200)-1)),'Community Info'!$F$3,0),0),ROW($1:$200)),ROW($1:$200))-1,""),$O$2))+'Community Info'!$K$3

For available date:

Formula:

=INDEX('Paste Availability Here'!$J$1:$J$200,SMALL(IFERROR(SMALL(IF(IFERROR(MATCH(--TRIM(LEFT('Paste Availability Here'!$B$1:$B$200,SEARCH(" Sq",'Paste Availability Here'!$B$1:$B$200)-1)),'Community Info'!$F$3,0),0),ROW($1:$200)),ROW($1:$200))-1,""),$O$2))

All committed CSE of course.

Updated workbook attached.

PS. While I was busy typing I missed Ford's explanation on the "--". Should have refreshed 1st. LOL

Nice 1 FT

the "--" is not actually on the TRIM, it is on the LEFT

LEFT/MID/RIGHT a;; produce text, not a value/number.

To conver a text number to a real number, there are a few different ways...
1. *1
2. +0
3. -- (double negative), kinda the same as addong 0, it converts it to a negative, then converts that to a positive
(yes, 2 wrongs (negatives) do make a right (positive) Bart lol)

OK try this...

I added a helper column to P-A-H sheet in S10 and copied this down...
=INDEX('Paste Availability Here'!$J$10:$J$200,SMALL(IF('Paste Availability Here'!$S$10:$S$200=--MID(B$4,FIND(",",B$4,1)+1,5),ROW('Paste Availability Here'!$S$10:$S$200)-10),ROWS($A$1:A1)))

then I used this ARRAY formula which will work when copied down in the 1st "set" (D6:D8)...
=INDEX('Paste Availability Here'!$J$10:$J$200,SMALL(IF('Paste Availability Here'!$S$10:$S$200=--MID(B$4,FIND(",",B$4,1)+1,5),ROW('Paste Availability Here'!$S$10:$S$200)-10),ROWS($A$1:A1)))

The boldedc parts will need to be re=set for the 1st row of each "set"

(I could probably work a way to do that automatically, but the formula would also probably get really complex