Return a value from a range

K

kgaumard

New Member
Joined
Sep 16, 2010
Messages
17
I am inputing 3 separate parameters (type, size and temperature) and I would like to creat a code that would concatenate these 3 elements, then lookup in a the 1st column of a 2 column range to return a value in the 2nd column of this range for exemple I enter:

type2
size3
temperature4

He would then concatenate all 3 elements into type2_size3_temperature4, look up for this value in the first column of the range and return the corresponding item in the 2nd column - item 3 in the example below
the range would be something like this
<TABLE style="WIDTH: 305pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=407 border=0><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 195pt; mso-width-source: userset; mso-width-alt: 9508" width=260><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 110pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: #1f497d" width=147 height=15>Concatenate</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 195pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #1f497d" width=260>Item1</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>type1_size1_temperature4</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item1</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>type2_size3_temperature2</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item2</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>type2_size3_temperature4</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item3</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>type3_size3_temperature4</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item4</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>type4_size4_temperature4</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item5</TD></TR></TBODY></TABLE>

Any help would be greatly appreciated.
Thank you.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
kgaumard,


Excel Workbook
ABCDE
1type2Item3type1_size1_temperature4Item1
2size3type2_size3_temperature2Item2
3temperature4type2_size3_temperature4Item3
4type3_size3_temperature4Item4
5type4_size4_temperature4Item5
6
Sheet1
 
Upvote 0
kgaumard,


The formula in cell B1, Index Match, may be easier to use.


Excel Workbook
ABCDE
1type2Item3type1_size1_temperature4Item1
2size3type2_size3_temperature2Item2
3temperature4type2_size3_temperature4Item3
4Item3type3_size3_temperature4Item4
5type4_size4_temperature4Item5
6
Sheet1
 
Upvote 0
Thank you for the reply,

However I want to use a macro a and create a user form. Also this is just a very small sample of the data and the actual data set is probably closer to 2,000 rows.

I am looking to use adavance filter options and get a unique list option based on the selection of the first drop down list. I am kind of stuck on this issue so any help would be good
 
Upvote 0
kgaumard,

I do not have a lot of experience with user forms.

Click on the Post Reply button, and just add BUMP in the message area, and someone else will assist you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
829
gvsumich
G
P
  • Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
212
John_w
J
E
  • Question
VBA for Web Scraping help
Replies
0
Views
593
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,975
Messages
6,122,538
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top