Sep 30 2019 09:17 AM
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 | ||||||||
AWG | 60 | 75 | 90 | 110 | Column | Number | AWG | |
14 | 25 | 30 | 35 | 40 | 60 | 24 | 14 | |
12 | 30 | 35 | 40 | 45 | 75 | 52 | 8 | |
10 | 40 | 50 | 55 | 65 | 90 | 38 | 12 | |
8 | 60 | 70 | 80 | 90 | 110 | 96 | 6 | |
6 | 80 | 95 | 105 | 120 | ||||
4 | 105 | 125 | 140 | 160 | ||||
3 | 120 | 145 | 165 | 185 | ||||
2 | 140 | 170 | 190 | 215 | ||||
1 | 165 | 195 | 220 | 245 |
Oct 01 2019 08:13 AM
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 !!
Oct 01 2019 02:03 PM
As variant for data like this
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