Home

Excel 2010 Reverse VLookUp

%3CLINGO-SUB%20id%3D%22lingo-sub-882905%22%20slang%3D%22en-US%22%3EExcel%202010%20Reverse%20VLookUp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882905%22%20slang%3D%22en-US%22%3E%3CP%3EPeeps%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20accomplish%20a%20reverse%20vlookup%20using%20Excel%202010.%26nbsp%3B%20Here%20is%20what%20it%20looks%20like.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%205%20columns%20and%2025%20rows.%26nbsp%3B%20The%202nd%20through%205%20columns%20are%20the%20raw%20data%20that%20I%20want%20to%20sort%20through%2C%20returning%20the%20data%20in%20column%201.%26nbsp%3B%20The%20two%20variables%20(held%20in%20other%20cells)%20is%20which%20column%20to%20look%20at%20and%20what%20the%20number%20I%20am%20looking%20for%20(the%20number%20must%20be%20less%20than%20or%20equal%20to%20the%20raw%20data%20in%20the%20column).%26nbsp%3B%20Then%20it%20spits%20out%20the%20resultant%20from%20column%201.%26nbsp%3B%20I%20can%20reorder%20the%20columns%20but%20do%20not%20want%20to%20break%20up%20the%20table%20into%204%20separate%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20sort%20of%20function%20can%20be%20used%20for%20this%3F%26nbsp%3B%20See%20below%20for%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ETABLE%201%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAWG%3C%2FTD%3E%3CTD%3E60%3C%2FTD%3E%3CTD%3E75%3C%2FTD%3E%3CTD%3E90%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EColumn%3C%2FTD%3E%3CTD%3ENumber%3C%2FTD%3E%3CTD%3EAWG%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E35%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E60%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E35%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E45%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E75%3C%2FTD%3E%3CTD%3E52%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E55%3C%2FTD%3E%3CTD%3E65%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E90%3C%2FTD%3E%3CTD%3E38%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E60%3C%2FTD%3E%3CTD%3E70%3C%2FTD%3E%3CTD%3E80%3C%2FTD%3E%3CTD%3E90%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3CTD%3E96%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E80%3C%2FTD%3E%3CTD%3E95%3C%2FTD%3E%3CTD%3E105%3C%2FTD%3E%3CTD%3E120%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E105%3C%2FTD%3E%3CTD%3E125%3C%2FTD%3E%3CTD%3E140%3C%2FTD%3E%3CTD%3E160%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E120%3C%2FTD%3E%3CTD%3E145%3C%2FTD%3E%3CTD%3E165%3C%2FTD%3E%3CTD%3E185%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E140%3C%2FTD%3E%3CTD%3E170%3C%2FTD%3E%3CTD%3E190%3C%2FTD%3E%3CTD%3E215%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E165%3C%2FTD%3E%3CTD%3E195%3C%2FTD%3E%3CTD%3E220%3C%2FTD%3E%3CTD%3E245%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-882905%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-886435%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202010%20Reverse%20VLookUp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-886435%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417495%22%20target%3D%22_blank%22%3E%40Gio_Barone%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20paste%20following%20formula%20in%20I3%20and%20copy%20it%20down.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%24A%243%3A%24A%2411%2CMIN(IFERROR(MATCH(H3%2CINDIRECT(SWITCH(G3%2C60%2C%22B3%3AB11%22%2C75%2C%22C3%3AC11%22%2C90%2C%22D3%3AD11%22%2C110%2C%22E3%3AE11%22))%2B1%2C1)%2B1%2C1)%2CROWS(A3%3AA11)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20it%20works%20for%20you%20!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887284%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202010%20Reverse%20VLookUp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417495%22%20target%3D%22_blank%22%3E%40Gio_Barone%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20for%20data%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20432px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134992i3543FE38199C0C7B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20K3%20it%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLOOKUP(1%2C0%2FFREQUENCY(I3%2CINDEX(%24C%243%3A%24F%2411%2C0%2CMATCH(%24H3%2C%24C%242%3A%24F%242%2C0)))%2C%24B%243%3A%24B%2411)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gio_Barone
Occasional Visitor

Peeps,

 

I am trying to accomplish a reverse vlookup using Excel 2010.  Here is what it looks like.

 

I have a table with 5 columns and 25 rows.  The 2nd through 5 columns are the raw data that I want to sort through, returning the data in column 1.  The two variables (held in other cells) is which column to look at and what the number I am looking for (the number must be less than or equal to the raw data in the column).  Then it spits out the resultant from column 1.  I can reorder the columns but do not want to break up the table into 4 separate tables.

 

What sort of function can be used for this?  See below for an example.

 

TABLE 1    
AWG607590110 ColumnNumberAWG
1425303540 602414
1230354045 75528
1040505565 903812
860708090 110966
68095105120    
4105125140160    
3120145165185    
2140170190215    
1165195220245    
2 Replies

@Gio_Barone 

You can paste following formula in I3 and copy it down.  

 

=INDEX($A$3:$A$11,MIN(IFERROR(MATCH(H3,INDIRECT(SWITCH(G3,60,"B3:B11",75,"C3:C11",90,"D3:D11",110,"E3:E11"))+1,1)+1,1),ROWS(A3:A11)))

 

 

hope it works for you !! 

@Gio_Barone 

As variant for data like this

image.png

in K3 it could be

=LOOKUP(1,0/FREQUENCY(I3,INDEX($C$3:$F$11,0,MATCH($H3,$C$2:$F$2,0))),$B$3:$B$11)

and drag it down

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies