I have a table of data:
Nom. Min. Max. Part #
0.25 300 580 25L177
0.25 300 800 25M177
0.50 750 1800 50L177
0.50 1000 2750 50M177
I want the user to be able to plug in 2 values:
Nom. 0.25
Load 600
I want a cell that will spit out the value in column 4 that looks up the value in column 1 (0.25) then finds the appropriate range using columns 2 and 3 (300 800) and returns the part number (25M177)
Vlookup only returns the first instance of 0.25 and doesnt allow me to use a range found in two seperate columns. Is there a way to do this? I have looked up various VB codes to help but have found nothing that works.
Assuming the table is in A1:D5,
Try:
=INDEX($D$2:$D$5,MATCH(1,INDEX(($A$2:$A$5=A11)*($B$2:$B$5<=A12)*($C$2:$C$5>=A12),0),0))
Where cell A11 contains the Nom. and A12 contains the Load to search for...
Adjust ranges and references to suit.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Works great so far! Thanks..will post another reply if I run into any trouble. I need to tweak it to meet a few other needs. I may need an iserror function in there because if the load is greater than the range specified there is 2 other columns that have higher load ranges so if there is an error searching the range it will search the range in 2 diff columns...but I should be able to figure that out.
Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks