Excell - Searching - Multiple Citerias 1

[blazblaz]

I want to find the value A1 in worksheet #1. This value will be found in
worksheet # 2, which has column A,B,C (some 500 records, already preentered values)

WORKSHEET # 2
A B C
1. 2.5 0.5 3
2. 2.5 0.7 4
3. 2.5 1.2 5
4. 3.0 0.8 4
5. 3.0 0.9 5
6. 3.0 1.0 6
7. 3.0 1.3 7
8. 4.0 1.0 6
9. 4.0 2.0 7
10. 4.0 2.5 8

The value A1 is the function of A1= C[Func(A,B)] from worksheet #2. Example:
Criteria is to find the first record C in worksheet #2 which will satisfy A>=2.7, and B<=1.1.
First A>2.7 or A=2.7 is record # 4. (The rest of records satisfy also this conditon) Second condition first B<1.1 or B=1.1 is record is record # 6. (record # 4,5 also satisfies this requirements, but the first is rec # 6)
So, the record # 6 is the first record wich will satsify A>=2.7 and B<=1.1. For this record value C=6.
This value is what has to appear in A1 in worksheet # 1.

How to solve this problem?

 

[DaleWatson123321]

blaz,

Your situation was a little challenging, so I took up the challenge and created a model that works.

I'm heading to bed shortly, so if you want to email me (at HOME) right away, I can then email you the file NOW.

Otherwise, you'll have to email me at my OFFICE tomorrow.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[plantj]

One simplifying first step would be to sort by *descending* B since you specify largest B as coming first. It then becomes trivial to do the lookup.

 

[blazblaz]

Thank you very much Dale, the program which you created works exactly as I wanted. You worth a few stars, I hope that you will help me in the future. You are a huge Excell expert, as I followed you in the past on this site.
Thanks again

 

[ETID]

Hi Dale,

Any chance you can post the steps that you took for all to see, or is it a complex/visual thing?

 

[DaleWatson123321]

A follow-up - for anyone interested...

It was suggested by &quot;plantj&quot; that the solution to this particular task could be made &quot;trivial&quot; - to do the lookup by sorting column B in descending order.

On the surface, this appears to be a possible solution. However, I would respectfully suggest this is NOT the case. I tried it and it didn't work.

If anyone wants a challenge, attempt to come up with the required solution for the specific matrix provided by &quot;blazblaz&quot; and the suggestion of &quot;plantj&quot;.

I don't mind being proven wrong, and a STAR is your reward from me.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

ETID,

Coincidence or what - we both seemed to post at the same time.

And it would appear you've already recognized that plantj's suggestion does not work.

The solution I used... This will be a &quot;rough&quot; explanation, as I don't have the file &quot;at hand&quot;.

It included a MATCH function, an extraction of Column B data to a second sheet, and a LOOKUP function to look up the &quot;isolated&quot; data on the second sheet.

I know this is a &quot;crude&quot; explanation, so here's an alternative...

I have my original file at work - the one that includes the (fictitious) data presented by blazblaz above. So if anyone would like a copy, email me at work, and I'll return the file.

Regards, ...Dale Watson WORK: dwatson@bsi.gov.mb.ca