Results 1 to 6 of 6
Thread: Lookup (2000)

20071129, 18:43 #1
 Join Date
 May 2003
 Location
 Manchester, Gtr Manchester, England
 Posts
 552
 Thanks
 0
 Thanked 0 Times in 0 Posts
Lookup (2000)
Edited by HansV to present data in table format
Hello I have a table on one sheet, I would like to return on a row in another a few cell values from a row in the table. The 'chosen' row will be driven by two cell values in that row...in context:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>1</td><td>XX</td><td>YY</td><td align=right>30</td><td align=right>40</td><td align=center>2</td><td>AA</td><td>BB</td><td align=right>30</td><td align=right>10</td><td align=center>3</td><td>EE</td><td>FF</td><td align=right>40</td><td align=right>10</td><td align=center>4</td><td>ZZ</td><td>LL</td><td align=right>40</td><td align=right>9</td></table>I need to return a row for values in columns A and B to my other sheet for the lowest cell value in D provided the value in C for that row is 30. I.e from this table I need AA in A2 and BB B2 to be displayed on my other sheet since 10 is the lowest from D where the C value is 30.
Hope this makes sense, many thanks Darren.

20071129, 18:46 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Lookup (2000)
I'd use some intermediate formulas. See attached demo.

20071130, 12:04 #3
 Join Date
 May 2003
 Location
 Manchester, Gtr Manchester, England
 Posts
 552
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Lookup (2000)
<P ID="nt"><font size=1>(No Text)</font>

20071130, 13:12 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Lookup (2000)
1) The intermediate formulas are array formulas, i.e. you have to confirm them with Ctrl+Shift+Enter. You can see that they are array formulas in my sample workbook because there are braces { } around the formula. (Note: you shouldn't type those braces yourself, you must confirm the formula with Ctrl+Shift+Enter to make it into an array formula)
2) Your formulas are inconsistent.
In cell E31 on Sheet1, you refer to L10:L1010 and M10:M1010, but then in E32 you refer to H31:H354 and I31:I35
Also, you use E30&""&31 instead of E30&""&E31

20071202, 16:02 #5
 Join Date
 May 2003
 Location
 Manchester, Gtr Manchester, England
 Posts
 552
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Lookup (2000)
thanks Hans, now resolved. First time I have used arrays so a valuable lesson has been had.
Kind regards Darren.

20071202, 21:46 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Lookup (2000)
Observe that I added another record to the sample you provided.
Sheet2...
B1: 30
B2: 1
B3:
Control+shift+enter...
=SUM(IF(Sheet1!C2:C6=B1,IF(Sheet1!D26=MIN(IF(Sheet1!C2:C6=B1,Sheet1!D26)),1)))
A5:
Control+shift+enter...
=IF(ROWS($A$5:A5)<=$B$3,SMALL(IF(Sheet1!$C$2:$C$6= $B$1,IF(Sheet1!$D$2:$D$6=MIN(IF(Sheet1!$C$2:$C$6=$ B$1,Sheet1!$D$2:$D$6)),ROW(Sheet1!$D$2:$D$6)ROW(Sheet1!$D$2)+1)),ROWS($A$5:A5)),"")
and copy down.
B5, copy across then down:
=IF(N($A5),INDEX(Sheet1!A$2:A$6,$A5),"")Microsoft MVP  Excel