Vlookup to match index

[emily2309]

Can someone help me change the following Vlookup into a Match Index formula please? I don't understand how to do it. I have to include a Match Index formula in my spreadsheet.

<link rel="File-List" href="file:///C:%5CUsers%5CEmily%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

=VLOOKUP(C2,CARBVEG,3)




Thanks
<o></o>

 

[StuLux]

Something along the lines of this:

=Index(CARBVEG,Match(C2,CARBVEG,0),3)

 

[emily2309]

Just comes up with N/A.. I don't understand

 

[mikerickson]

Replacing a nice neat VLOOKUP (with named Range ) sounds very homework'ish, so..

Did you know that if one of the indexing arguments of INDEX is 0 (or omitted), it returns not one element of the array, but a whole row or column.
e.g. INDEX(CARBVEG, , 3) will return the entire third column of the named range.

(Hint, which column of the named range do you want to use as the lookup_array argument for the MATCH function?)

 

[emily2309]

It's for my A2 spreadsheet project actually. I need to use it.

 

[Akashwani]

Hi Emily,

Take a look here....

http://www.youtube.com/user/ExcelIsFun#p/search/21/CHk7a8FM9TI

It may not be what you want, but there are loads of other videos.

Ak

 

[Aladin Akyurek]

Can someone help me change the following Vlookup into a Match Index formula please? I don't understand how to do it. I have to include a Match Index formula in my spreadsheet.

<LINK rel=File-List href="file:///C:%5CUsers%5CEmily%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><STYLE> <!-- /* Font Definitions */ @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </STYLE>

=VLOOKUP(C2,CARBVEG,3)




Thanks
<?xml:namespace prefix = o /><o></o>

If you must use the named range (CARBVEG) as is:

=INDEX(CARBVEG,MATCH(C2,INDEX(CARBVEG,0,1),1),3)

Let's CARBVEG stand for F2:H100. The formula would become:

=INDEX($H$2:$H$100,MATCH(C2,$F$2:$F$100,1))