Complicated Lookup

[Iknewthisguy]

Good afternoon,

I have a destination starting in A1:B7 that looks like this (this is just sample data):

Valentia
           1x1a
           1x1b
           2x2a
           2x2b
           3x2a
           3x2b

Then I have data that looks like this (that occupies D1:G7)

Woodbridge
           1x1a      850      1267
           2x2b      1025    1650
Valentia
           1x1b      735      1300
           2x2b      1100    1675
           3x2b      1300    1850

The second set of data is populated via a query and therefore I need to sort it dynamically. Not only is a potential problem (as sometimes Woodbridge will have a 2x2b populated and sometimes it won't even have a line representing where data is supposed to be) but I also need caccount for the fact that the names of the floorplans (i.e. 1x1a, 2x2b,etc...) are repeated. Ideally I'd have some sort of lookup that find Woodbridge, then find the 1x1a that occurs before Valentia is populated within that same column as Woodbridge.

My ultimate goal is to retrieve the data in the two columns located in F & G.

I realize this is complicated and I don't know that I was able to explain the problem very well, but would greatly appreciate any help that can be provided.

Thanks.
Chris

 

[rsulliva]

Use the following formula after completing the following steps.

1. Select entire col D
2. Hit F5
3. Select "Special" Button
4. Select "Blanks" then OK
5. Hit up arrow once
6. Type "="
7. Hold ctrl key and hit enter key
Now lets get rid of the formulas this generated.
8. Select entire col D again
9. Hit ctrl C
10. Hit Alt E
11. Hit Alt S
12. Hit Alt V
13. Hit Enter

Now to lookup a value given the criteria where A1 contains Valentia (criteria 1) and B1 contains 1x1a (criteria 2) enter the following in c2 or wherever you want:

={INDEX(F1:F7,MATCH(1,(D1:D7=$A$1)*(E1:E7=B2),0))}

the brackets {} are automatically entered for your by hitting ctrl shift enter. It is an array formula. This looks up your data in col F. Change the formula to meet your specifications.

This should give you a good start.

Enjoy

rsulliva

 

[Iknewthisguy]

rsulliva,

Thanks for your help. I gave it a run and am getting an N/A#. It looks like it's because I've got multiple occurrences of 1x1a. This is really where my catch is, singling out occurrences of 1x1a (or any other floor plan for that matter) per property.

Any other ideas?

 

[rsulliva]

Iktg,

The formula I provided does work with many of the same values in col E as long as, for example, there is only one instance of Valentia next to one instance of 1x1a. If you have a thousand 1x1a next to a thousand various other models (woodbridge, Etc), the correct value would always be returned given the model and floorplan have only one occurance of being next to each other. Make sure that you have copied the formula exactly as written, and then hold ctrl shift down at the same time and enter. You should have {} brackets around the entire formula after you hit enter. No brackets and you will get an N/A# every time. The computer will put the brackets automatically if done correctly.

=INDEX(F1:F7,MATCH(1,(D1:D7=$A$1)*(E1:E7=B2),0))

After ctrl shift enter it will look like this:
{=INDEX(F1:F7,MATCH(1,(D1:D7=$A$1)*(E1:E7=B2),0))}

where F1:F7 contains the data to return
where D1:D7 is the first lookup range
where A1 is the data to lookup within the first range
where E1:E7 is the second lookup range
where B2 is the data to lookup within the second range

modify to suit your data set.

Coupled with the instruction I posted previously, this will work.

Try again.....