Complex look up

  • Thread starter Thread starter bill.shut
  • Start date Start date
B

bill.shut

I have in worksheet 1, WS1, a list of values like Ag_sci, Appl_sci,
Arts, Educ_Dean, etc. in Column A.

In worksheet 2, WS2, I have a table A3:J50. I want to be able to find
the value from the list in WS1 that is somewhere in the WS2 table and
return the left most column value. For example, Arts is a unique value
but could be in any cell in B3:J50. If I find Arts, I need the value
in column A in the table in WS2. That is, if in WS2 "Arts" is in D9,
then I need the value in A9, if it is in F33, I need the value in A33.
When the formula finds Arts in WS2 table, i need it to return whatever
value is in Column B for the row that matches my value in WS1 Column A.

Hope I explained this well enough. Thanks in advance for your hep.
 
Hi!

Try this...

Assume your list in WS1 starts in A2.

In B2 enter this formula with the key combo of CTRL,SHIFT,ENTER:

=INDEX(WS2!A3:A50,LARGE(IF(WS2!B3:J50=A2,ROW(1:48)),1))

Row(1:48) refers to the number of rows in the range WS2!A3:J50

If there may be dupes within the table then we'll need to try something else
although you did say:
Arts is a unique value ...
Click to expand...

Biff
 
Say you list in WS1 starts at A1.
Enter this *array* formula in B1, and copy down as needed:

=INDEX(WS2!$A$1:$A$50,MAX(IF(A1=WS2!$B$3:$J$50,ROW(WS2!$B$3:$J$50))))
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Duplicates will return the highest row entry (last entry).
If you would like duplicates to return the first entry, change MAX() to
MIN().
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Let me copy part of Biff's formula to make this a little shorter:

=INDEX(WS2!$A$1:$A$50,MAX(IF(A1=WS2!$B$3:$J$50,ROW($3:$50))))
 
Back
Top