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?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Try this...
In G14
=IFERROR(VLOOKUP(G13,'data tables'!B4:C21,2),"")

Open in new window

0
 
Neil FlemingIndependent consultantCommented:
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 FlemingIndependent consultantCommented:
Oh, sorry, Neeraj.. already answered it. However the question about the axial loads still applies.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 FlemingIndependent consultantCommented:
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 FlemingIndependent consultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.