Excel 2010 Reverse VLookUp

Copper Contributor

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