Match values against a range

G

geniechua

New Member
Joined
Jun 6, 2012
Messages
22
Dear all,

I have a table with a position code and 3 columns of data (append below).

<TABLE style="WIDTH: 210pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=280 border=0 x:str><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" width=88 height=17>Position Code</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=64 x:num="0.25">
1

​
</TD><TD class=xl29 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=64 x:num="0.5">2
​
</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=64 x:num="0.75">3
​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CBAM_HS
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>201255
​
</TD><TD class=xl30 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>217900
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>233400
​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CBAM_SM
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>116828
​
</TD><TD class=xl30 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>130000
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>195910
​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CBAM_M
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>84053
​
</TD><TD class=xl30 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>100613
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>135600
​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CBAM_TL
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>60000
​
</TD><TD class=xl30 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>71625
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>82200
​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CBAM_SO
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>48680
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>56376
​
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>62400
​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CBAM_O

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
​
</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>27147

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CBAM_JO</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

I'd like to lookup the position code plus a random value and get excel to return the column of which the random value is smaller.

For example,

CBAM_SO 48600 -> To return "1"
CBAM_O 27100 -> To return "2"
CBAM_TL 83000 -> To return "4"

If there are no data for the position code, I'd like it to return as "NM"

Are there any formula which I can use to accomplish this?


cheers,
Jean
 
Sort by date Sort by votes
Hi guys,

I think I might be onto a possible solution but am still missing one formula to make it work the way I want it to.

Position Code</SPAN>1</SPAN>2</SPAN>3</SPAN>4</SPAN>
CBAM_HS</SPAN>201255</SPAN>217900</SPAN>233400</SPAN>9999999</SPAN>
CBAM_SM</SPAN>116828</SPAN>130000</SPAN>195910</SPAN>9999999</SPAN>
CBAM_M</SPAN>84053</SPAN>100613</SPAN>135600</SPAN>9999999</SPAN>
CBAM_TL</SPAN>60000</SPAN>71625</SPAN>82200</SPAN>9999999</SPAN>
CBAM_SO</SPAN>48680</SPAN>56376</SPAN>62400</SPAN>9999999</SPAN>
CBAM_O</SPAN> 27147</SPAN>9999999</SPAN>
CBAM_JO</SPAN>
Column</SPAN>
CBAM_SO</SPAN>48682</SPAN>2</SPAN>
CBAM_O</SPAN>56375</SPAN>3</SPAN>
CBAM_JO</SPAN>23659</SPAN>NM</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=4></COLGROUP>


The formula I used is:

=IF(AND(ISBLANK(B6),ISBLANK(C6),ISBLANK(D6),ISBLANK(E6)),"NM",MATCH(INDEX(B6:E6,MATCH(SMALL(B6:E6,COUNTIF(B6:E6,"< "&B11)+1),B6:E6,0)),B6:E6,TRUE))

What I need now is to be able to lookup the position code in the matrix so that it gives me the above results.

I've tried both Lookup and Vlookup but keep getting a #VALUE error...

Any ideas anyone?
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
geniechua said:
Hi vlady,

there's still an error with the formula...could it be this section?

(TRUE,INDEX(H2<INDEX($B$2:$d$7,match(g2,$a$2:$a$7,0),0),0),0))),
</INDEX($B$2:$d$7,match(g2,$a$2:$a$7,0),0),0),0))),
"NM",

GDH,

I don't really understand your solution. I ran into an error message when I tried using CTRL + SHIFT + F3.
Click to expand...

This is probably what pgc01 has suggested:

=IFERROR(INDEX($B$1:$D$1,MATCH(TRUE,INDEX(H2 < INDEX($B$2:$D$7,MATCH(G2,$A$2:$A$7,0),0),0),0)),"NM")

which needs to be confirmed with control+shift+enter, not just with enter.
 
Upvote 0
Hi Aladin,

Understand from vlady that Excel 2003 doesn't recognise IFERROR?

Is there any way else around it?
 
Upvote 0
Aladin Akyurek said:
This is probably what pgc01 has suggested:

=IFERROR(INDEX($B$1:$D$1,MATCH(TRUE,INDEX(H2 < INDEX($B$2:$D$7,MATCH(G2,$A$2:$A$7,0),0),0),0)),"NM")

which needs to be confirmed with control+shift+enter, not just with enter.
Click to expand...

geniechua said:
Hi Aladin,

Understand from vlady that Excel 2003 doesn't recognise IFERROR?

Is there any way else around it?
Click to expand...

Add the following code in VBA as a module to your workbook:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Now invoke:
Code: 
=IF(ISNUMBER(V(INDEX($B$1:$D$1,
     MATCH(TRUE,INDEX(H2 < INDEX($B$2:$D$7,MATCH(G2,$A$2:$A$7,0),0),0),0)))),
        V(),"NM")
confirmed with control+shift+enter.
 
Upvote 0
Hi Aladin,

I tried as you've suggested but now the only result that came back was "NM", even when it's supposed to be "2" or "3"...
 
Upvote 0
geniechua said:
Hi Aladin,

I tried as you've suggested but now the only result that came back was "NM", even when it's supposed to be "2" or "3"...
Click to expand...

I just checked...
Position Code
1
2
3
CBAM_HS
201255
217900
233400
CBAM_SO
48600
1
CBAM_SM
116828
130000
195910
CBAM_O
27100
2
CBAM_M
84053
100613
135600
CBAM_TL
83000
NM
CBAM_TL
60000
71625
82200
CBAM_SO
48680
56376
62400
CBAM_O
27147

<tbody>
</tbody>

I2, just enter:
Rich (BB code): 
=IF(ISNUMBER(V(INDEX($B$1:$D$1,MATCH(TRUE,INDEX(H2 < INDEX($B$2:$D$7,
    MATCH(G2,$A$2:$A$7,0),0),0),0)))),V(),"NM")
Or, control+shift+enter, not just enter:
Rich (BB code): 
=IF(ISNUMBER(V(INDEX($B$1:$D$1,MATCH(TRUE,H2 < INDEX($B$2:$D$7,
    MATCH(G2,$A$2:$A$7,0),0),0)))),V(),"NM")
both work as intended.

I'd suggest the second formula in case you run against the limit of nested levels.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
388
smozgur
smozgur
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question
VBA code clean up
Replies
9
Views
326
mumps
mumps
E
  • Question
VBA for Web Scraping help
Replies
0
Views
819
epactheactor
E

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,399
Latest member
alchavar

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