|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 24th, 2010, 06:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup possibly ?
I have this data in rows C, & L thru P. In C6, if I manually enter a 3,
I need the data in row 17 to populate row 11 as shown (L11="", M11="",N11=-972, O11=-975 and P11=-974. And if I manually enter a 5 in C6, I need the data in row 19 to populate row 11 as shown (L19 = -589, M19= -582, N19 = -582, O19= - 585, & P19 = -584. In other words, I want formulas in row 11 to lookup whats in C3, and find that number in C15:C19, and then populate the numbers to the right in the appropriate row( either 15, 16, 17, 18 or 19), and have them in row 11. I hope this makes sense. Thank you, Steve C L M N O P In C6 -3 Row 11 -972 -975 -974 C15 =1 -2921 C16 =2 -1461 -1460 C17=3 -972 -975 -974 C18=4 -729 -729 -732 -731 C19=5 -589 -582 -582 -585 -584 
|
|
#2
March 24th, 2010, 07:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup possibly ?
Put this in L11:
=IF(OR($C$6="",$C$60,$C$65),"",IF(INDEX(L$15:L$1 9,MATCH($C$6,$C$15:$C $19,0))="","",INDEX(L$15:L$19,MATCH($C$6,$C$15:$C$ 19,0)))) Then you can copy it across to P11. Hope this helps. Pete On Mar 24, 6:55*pm, Steve wrote: I have this data in rows C, & *L thru P. In C6, if I manually enter a 3, I need the data in row 17 to populate row 11 as shown (L11="", M11="",N11=-972, O11=-975 and P11=-974. And if I manually enter a 5 in C6, I need the data in row 19 to populate row 11 as shown (L19 = -589, M19= -582, N19 = -582, O19= - 585, & P19 = -584. In other words, I want formulas in row 11 to lookup whats in C3, and find that number in C15:C19, and then populate the numbers to the right in the appropriate row( either 15, 16, 17, 18 or 19), and have them in row 11. I hope this makes sense. Thank you, Steve C * * * * * * * * L * * * *M * * * * * *N * * * * * * * O * * * * * * *P In C6 *-3 * * * Row 11 * * * * * * * * * * * * * * -972 * * * * *-975 * * * *-974 C15 =1 * * * * * * * * * * * * * * * * * * * -2921 C16 =2 * * * * * * * * * * * -1461 * * * * * -1460 C17=3 * * * * * * * -972 * * * * * *-975 * * * * * *-974 C18=4 * * * * * *-729 * * * *-729 * * * * * -732 * * * * * *-731 C19=5 * * -589 * *-582 * * *-582 * * * * * *-585 * * * * * *-584
|
|
#3
March 24th, 2010, 08:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup possibly ?
Perfect. I hadn't given you all the ranges ( I actually had data through row
25, and C6 could've gone up to 13, so when I adjusted your formula as below in D11 and dragged it to P11, it all worked EXACTLY how I was hoping. =IF(OR($C$6="",$C$60,$C$613),"",IF(INDEX(D$13  $ 25,MATCH($C$6,$C$13:$C$25,0))="","",INDEX(D$13$2 5,MATCH($C$6,$C$13:$C$25,0))))Thanks again, Steve "Pete_UK" wrote: Put this in L11: =IF(OR($C$6="",$C$60,$C$65),"",IF(INDEX(L$15:L$1 9,MATCH($C$6,$C$15:$C $19,0))="","",INDEX(L$15:L$19,MATCH($C$6,$C$15:$C$ 19,0)))) Then you can copy it across to P11. Hope this helps. Pete On Mar 24, 6:55 pm, Steve wrote: I have this data in rows C, & L thru P. In C6, if I manually enter a 3, I need the data in row 17 to populate row 11 as shown (L11="", M11="",N11=-972, O11=-975 and P11=-974. And if I manually enter a 5 in C6, I need the data in row 19 to populate row 11 as shown (L19 = -589, M19= -582, N19 = -582, O19= - 585, & P19 = -584. In other words, I want formulas in row 11 to lookup whats in C3, and find that number in C15:C19, and then populate the numbers to the right in the appropriate row( either 15, 16, 17, 18 or 19), and have them in row 11. I hope this makes sense. Thank you, Steve C L M N O P In C6 -3 Row 11 -972 -975 -974 C15 =1 -2921 C16 =2 -1461 -1460 C17=3 -972 -975 -974 C18=4 -729 -729 -732 -731 C19=5 -589 -582 -582 -585 -584 .
|
|
#4
March 25th, 2010, 02:04 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup possibly ?
You're welcome, Steve - glad it worked for you.
Pete On Mar 24, 8:21*pm, Steve wrote: Perfect. I hadn't given you all the ranges ( I actually had data through row 25, and C6 could've gone up to 13, so when I adjusted your formula as below in D11 and dragged it to P11, it all worked EXACTLY how I was hoping. =IF(OR($C$6="",$C$60,$C$613),"",IF(INDEX(D$13 $ 25,MATCH($C$6,$C$13:$C$25*,0))="","",INDEX(D$13$ 25,MATCH($C$6,$C$13:$C$25,0))))Thanks again, Steve "Pete_UK" wrote: Put this in L11: =IF(OR($C$6="",$C$60,$C$65),"",IF(INDEX(L$15:L$1 9,MATCH($C$6,$C$15:$C $19,0))="","",INDEX(L$15:L$19,MATCH($C$6,$C$15:$C$ 19,0)))) Then you can copy it across to P11. Hope this helps. Pete On Mar 24, 6:55 pm, Steve wrote: I have this data in rows C, & *L thru P. In C6, if I manually enter a 3, I need the data in row 17 to populate row 11 as shown (L11="", M11="",N11=-972, O11=-975 and P11=-974. And if I manually enter a 5 in C6, I need the data in row 19 to populate row 11 as shown (L19 = -589, M19= -582, N19 = -582, O19= - 585, & P19 = -584. In other words, I want formulas in row 11 to lookup whats in C3, and find that number in C15:C19, and then populate the numbers to the right in the appropriate row( either 15, 16, 17, 18 or 19), and have them in row 11. I hope this makes sense. Thank you, Steve C * * * * * * * * L * * * *M * * * * * *N * * * * * * * O * * * * * * *P In C6 *-3 * * * Row 11 * * * * * * * * * * * * * * -972 * * * * *-975 * * * *-974 C15 =1 * * * * * * * * * * * * * * * * * * * -2921 C16 =2 * * * * * * * * * * * -1461 * * * * * -1460 C17=3 * * * * * * * -972 * * * * * *-975 * * * * * *-974 C18=4 * * * * * *-729 * * * *-729 * * * * * -732 * * * * * *-731 C19=5 * * -589 * *-582 * * *-582 * * * * * *-585 * * * * * *-584 .- Hide quoted text - - Show quoted text -
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
