EXCEL - Find a value in a table/range and return a corresponding value/text

What you probably want to do is a VLookUp().  Actually, since you have 365, look in to XLookUp().  It is a new function that mashes together several old features (really cool, learn one command instead of a bunch of lookups).

.

As Ross suggested, if you provide us with some simple sample data, we can provide a more specific answer.  Include some starting point data and manually typed examples of what you want the "answer" to look like.

.

As a starting point, here are some articles about VLookup

!   MS Quick Reference Card: VLOOKUP refresher- 3 pg
https://support.office.com/en-us/article/quick-reference-card-vlookup-refresher-750fe2ed-a872-436f-92aa-36c17e53f2ee
VLOOKUP is one of the most popular, useful functions in Excel, but it’s hard to remember the formula if you don’t use it that often.
Download a reference card that explains what the arguments mean and how to use them, click the link below. Feel free to print a copy of the card, or save it somewhere on your computer to refer to later.
https://download.microsoft.com/download/9/b/4/9b49c8c5-d7a9-45b1-b8b6-52067e9970a8/AF101984660_en-us_xl_qrc_vlookup%20refresher.pdf
 .

Ultimate Guide to VLOOKUP             2015 11 07             Matthew Kuo
http://www.mbaexcel.com/excel/the-ultimate-guide-to-vlookup/
Learning the VLOOKUP formula is, for most people, the first step taken towards becoming an advanced Excel user.  For me, this milestone actually occurred in 2004 during an internship at Microsoft in the company’s Commercial Operations Division.  One of the senior analysts showed me the ropes by teaching me the formula.  While I was still getting familiar with spreadsheets in general, learning that formula was definitely the first time I started to understand the true power of Excel.
VLOOKUP is by no means perfect, but the formula has stood the test of time because to its usefulness and intuitiveness.  Even though most Excel pundits agree that INDEX MATCH is better than VLOOKUP, VLOOKUP is still several times more popular than any Excel formula out there, according to Google Trends.
Basics
Beginner’s Guide to Excel Formula Writing
.  *  Keys to Learning VLOOKUP
.  *  How to Use VLOOKUP
.  *  How to Use HLOOKUP
.  *  How to Use VLOOKUP’s Range Lookup Feature
.

! VLOOKUP Tutorial for Excel - Everything You Need To Know    2019 08 15
https://www.excelcampus.com/vlookup-challenge-training-series/
https://www.youtube.com/watch?v=d3BYVQ6xIE4&feature=youtu.be

@ Announcing XLOOKUP (365)               2019 08 28
https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/127917iF0B67B064B07CA2D/image-size/large
XLOOKUP is named for its ability to look both vertically and horizontally (yes it replaces HLOOKUP too!). In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). Let’s consider its signature in the simplest form:
          XLOOKUP(What you are looking for, Where to find it, What to return)
Advanced XLOOKUP variations
.     match_mode allows you to set the type of match you’d like to perform.
.     .     Use zero to perform an exact match, this is the default.
.     .     Use 1 or -1 to allow a match against the nearest smaller (or larger) item when there is no exact match.
.     .     Use 2 to do a simple wildcard match where ? means match any character and * means match any run of characters
search_mode lets you configure the type and direction of search
.     .     Use 1 or -1 to search from first-to-last or last-to-first.
.     .     Use 2 or -2 to do a binary search on sorted data. This is included for expert users only.
.

.

Here is a utility that can help you generate lookup commands

!  Vlookup Assistant for Excel – Preview/Beta (VBA tool)
https://www.excelcampus.com/functions/vlookup-assistant-preview/
https://www.youtube.com/watch?v=g2_Yu_VRQ9E&feature=youtu.be

.

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

.

Includes links to macros to randomize text in Word and numbers in Excel

.

This article describes another way of sharing Office files:

2020 03 09- Share a cloud stored document from Microsoft Office
https://office-watch.com/2020/share-cloud-stored-document-microsoft-office/
Using “Invites” to specific people from inside Office apps. Modify document access permissions and add a “note” with “how to” instructions for recipient

.

**************************************

.

The first 2 minutes of this video gives an example of What I mean by a “simple” example. Use short simple names and quantities, and just 2 or 3 rows per sample data you want calculations done on.  You want simple numbers so you can do the math checks in your head.

Col- Stacking Columns of Data (Unpivot, Split Columns by delimiter, and everything!)(PowerQuery)            2017 04 16
http://ozdusoleil.com/2017/04/16/power-query-stacking-columns-of-data-unpivot-split-columns-by-delimiter-and-everything/
We’ve got headers and a large number of paired columns that need to be condensed down to just 2 columns. 

.

**************************************

.

Vance,

Do you get your answer?

I have the same problem so I would like to see if you get the solution or not.

I tried the Xlookup, Index and Match but all return #Value or #NA

Here's a screenshot of what I'm trying to get Excel to do/find.

Based on the Weight & Distance criteria (in yellow), find the corresponding value in the table and report back the "minimum" size of crane needed to safely lift the load.

Thank you for submitting help, tried both VLOOKUP & XLOOKUP but dont believe either would work in my scenario (note the example of what I'm trying to do, posted this morning).

thank you

Here's a screenshot of what I'm trying to get Excel to do/find.

Based on the Weight & Distance criteria (in yellow), find the corresponding value in the table and report back the "minimum" size of crane needed to safely lift the load.

Sorry this chart does not make sense.

Why do you have multiple entries for the same distance / weight?

Why could it not be reduced to:

Are the values entered going to be exact matches or any value less than or equal too?

Do you want to ensure minimize crane size, since 30 ton can handle all weight / distances combos a 17 ton one can?

Sorry about the confusion. I want to have a separate worksheet where the user simply enters the criteria and let Excel go find the corresponding "answer" from a separate worksheet (will be hidden). I was just putting all data/info & search criteria on one screenshot for ease of viewing here.

The "multiple values" you may be noticing are just simply the data for different "stages" or "lengths" of Crane Boom sections (as found directly on the Manufacturers data sheets). I've taken the data directly from the manufacturer.

Not looking for exact match's, but looking for a minimum value that satisfies the search criteria.

Yes, only looking to find/ensure minimum crane size is found.

thank you