# 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
###### Who is Participating?

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.

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

Experts Exchange Solution brought to you by

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

Consultant 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
Consultant and developerCommented:
0
Excel & VBA ExpertCommented:
No problem Neil!

If you leave the last argument, it is True (approximate match) by default.
0
Author 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
Author 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
Excel & VBA ExpertCommented:
You're welcome Paul!
0
Consultant 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
Consultant 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
Finance 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.