Need to look up a value in a range and then return value from other column/cell.

In the attached, cell H13 in the 'Calcs' worksheet has a value of 2.2 (this is a dynamic value). I need this value to be compared to values in column b in the 'data tables' worksheet. Where it lands in the correct range I need the value from column C returning in cell G14. Got myself in a muddle as column b is a range rather than a specific value. It needs to do an iterative check, but no idea how. Regards Paul
scaffold-designer-v3.xlsm
vipamanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this...
In G14
=IFERROR(VLOOKUP(G13,'data tables'!B4:C21,2),"")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Neil FlemingConsultant and developerCommented:
Do you just need to do a VLOOKUP of the value in G13? Putting the following formula in G14:

=VLOOKUP(G13,'data tables'!B4:C21,2,TRUE)

The "TRUE" parameter at the end means "approximate match." This means that if you enter 2.3 instead of 2,2 in G13, you will still get the answer 25.3. That may be a problem if actually you need to choose the next higher load?
0
Neil FlemingConsultant and developerCommented:
Oh, sorry, Neeraj.. already answered it. However the question about the axial loads still applies.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No problem Neil!

If you leave the last argument, it is True (approximate match) by default.
0
vipamanAuthor Commented:
Just altered my data (to validate) to give a lift height of 3000mm and a value of 13.6 is returned. This is the next value up in table, it should be 15.2?
0
vipamanAuthor Commented:
Thanks you two. Realised my error on my last question. Lift height = 3000 but when effective length applied pushes it into the next band. Such a simple solution. Regards Paul
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Paul!
0
Neil FlemingConsultant and developerCommented:
I am still slightly worried, since this involves engineering safety... The VLOOKUP function in approximate mode returns the previous result from the table.

That is, the way it is set up, it is assigning the same "Safe load" to a 2.3m m tube as to a 2.2 m tube, even though the 2.3m is longer.

Shouldn't it for safety's sake assign the 2.4m version of the load, which is a lower 22, rather than the higher 25.3?

Unfortunately, it is hard to make VLOOKUP do this, I think..
0
Neil FlemingConsultant and developerCommented:
I guess for the intermediate numbers, though, you could round them up, thus:

=VLOOKUP((ROUNDUP(G13*10/2,0)*2/10),'data tables'!B4:C21,2,TRUE)

This forces the Vlookup to perform a lookup of 2.4 if you enter 2.3, or 2.2 if you enter 2.1.
0
Rob HensonFinance AnalystCommented:
Alternatively, to get the higher load value you can use a combo of INDEX and MATCH:

=INDEX('data tables'!$C$4:$C$21,MATCH(G13,'data tables'!$B$4:$B$21,-1))

However, the -1 parameter in the MATCH function needs the data to be sorted in descending order by tube length, ie the opposite way to how it is currently.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.