| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need lookup help
I need to lookup a value in a range based on a cell then return the value of
the 1st row of the column the found cell is in. here's my wooksheet 1 2 3 4 5 6 Employees Layer 7001 7005 7014 7001 returns 1 7001 7005 7015 7051 7002 returns 2 7001 7005 7015 7052 7003 ect 7001 7005 7029 7004 ect 7001 7005 7030 7005 7001 7005 7031 7036 7006 7001 7005 7031 7037 7007 7001 7002 7027 7008 7001 7002 7006 7017 7009 7001 7002 7006 7016 7010 7001 7002 7007 7011 7001 7002 7028 7032 7012 7001 7002 7028 7033 7013 7001 7002 7028 7034 7014 7001 7002 7028 7035 7015 7001 7002 7008 7018 7022 7016 7001 7002 7008 7018 7023 7017 7001 7002 7008 7018 7024 7018 7001 7002 7008 7019 7025 7019 7001 7002 7008 7019 7026 7020 7001 7003 7009 7021 7001 7003 7043 7022 7001 7003 7010 7020 7023 7001 7003 7010 7021 7024 7001 7003 7044 7045 7025 7001 7003 7044 7046 7026 7001 7003 7044 7047 7027 7001 7004 7013 7028 7001 7004 7040 7029 7001 7004 7011 7030 7001 7004 7038 7031 7001 7004 7012 7032 7001 7004 7039 7041 7033 7001 7004 7039 7042 7034 7001 7053 7049 7035 7001 7053 7050 7036 7001 7053 7048 7037 7038 7039 7040 7041 7042 7043 7044 7045 7046 7047 7048 7049 7050 7051 7052 7053 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need lookup help
Assuming you have your employees codes in in Col G, enter this formula in
H2..which will return the row number =MIN(IF($A$1:$F$1000=E1,ROW($A$1:$F$1000),"")) and copy that down as required Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Mike Coleman" wrote: I need to lookup a value in a range based on a cell then return the value of the 1st row of the column the found cell is in. here's my wooksheet 1 2 3 4 5 6 Employees Layer 7001 7005 7014 7001 returns 1 7001 7005 7015 7051 7002 returns 2 7001 7005 7015 7052 7003 ect 7001 7005 7029 7004 ect 7001 7005 7030 7005 7001 7005 7031 7036 7006 7001 7005 7031 7037 7007 7001 7002 7027 7008 7001 7002 7006 7017 7009 7001 7002 7006 7016 7010 7001 7002 7007 7011 7001 7002 7028 7032 7012 7001 7002 7028 7033 7013 7001 7002 7028 7034 7014 7001 7002 7028 7035 7015 7001 7002 7008 7018 7022 7016 7001 7002 7008 7018 7023 7017 7001 7002 7008 7018 7024 7018 7001 7002 7008 7019 7025 7019 7001 7002 7008 7019 7026 7020 7001 7003 7009 7021 7001 7003 7043 7022 7001 7003 7010 7020 7023 7001 7003 7010 7021 7024 7001 7003 7044 7045 7025 7001 7003 7044 7046 7026 7001 7003 7044 7047 7027 7001 7004 7013 7028 7001 7004 7040 7029 7001 7004 7011 7030 7001 7004 7038 7031 7001 7004 7012 7032 7001 7004 7039 7041 7033 7001 7004 7039 7042 7034 7001 7053 7049 7035 7001 7053 7050 7036 7001 7053 7048 7037 7038 7039 7040 7041 7042 7043 7044 7045 7046 7047 7048 7049 7050 7051 7052 7053 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need lookup help
1 2 3 4 5 6 Employees Layer
7001 7005 7014 7001 1 7001 7005 7015 7051 7002 2 7001 7005 7015 7052 7003 2 7001 7005 7029 7004 2 7001 7005 7030 7005 2 7001 7005 7031 7036 7006 2 7001 7005 7031 7037 7007 2 7001 7002 7027 7008 2 7001 7002 7006 7017 7009 2 7001 7002 7006 7016 7010 2 7001 7002 7007 7011 2 7001 7002 7028 7032 7012 2 7001 7002 7028 7033 7013 2 7001 7002 7028 7034 7014 2 7001 7002 7028 7035 7015 2 7001 7002 7008 7018 7022 7016 2 7001 7002 7008 7018 7023 7017 17 7001 7002 7008 7018 7024 7018 18 7001 7002 7008 7019 7025 7019 19 7001 7002 7008 7019 7026 7020 20 7001 7003 7009 7021 21 7001 7003 7043 7022 2 7001 7003 7010 7020 7023 2 7001 7003 7010 7021 7024 2 7001 7003 7044 7045 7025 2 7001 7003 7044 7046 7026 2 7001 7003 7044 7047 7027 2 7001 7004 7013 7028 2 7001 7004 7040 7029 2 7001 7004 7011 7030 2 7001 7004 7038 7031 2 7001 7004 7012 7032 2 7001 7004 7039 7041 7033 2 7001 7004 7039 7042 7034 2 7001 7053 7049 7035 2 7001 7053 7050 7036 2 7001 7053 7048 7037 2 7038 2 7039 2 7040 2 7041 2 7042 2 7043 2 7044 2 7045 2 7046 2 7047 2 7048 2 7049 2 7050 2 7051 2 7052 2 7053 2 not Quiet working perfectly. this is what I am getting. For example 7006 should return a layer of 3. He is what is shown in the formula. =MIN(IF($A$1:$F$1000=E6,ROW($A$1:$F$1000),"")) "Jacob Skaria" wrote: Assuming you have your employees codes in in Col G, enter this formula in H2..which will return the row number =MIN(IF($A$1:$F$1000=E1,ROW($A$1:$F$1000),"")) and copy that down as required Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Mike Coleman" wrote: I need to lookup a value in a range based on a cell then return the value of the 1st row of the column the found cell is in. here's my wooksheet 1 2 3 4 5 6 Employees Layer 7001 7005 7014 7001 returns 1 7001 7005 7015 7051 7002 returns 2 7001 7005 7015 7052 7003 ect 7001 7005 7029 7004 ect 7001 7005 7030 7005 7001 7005 7031 7036 7006 7001 7005 7031 7037 7007 7001 7002 7027 7008 7001 7002 7006 7017 7009 7001 7002 7006 7016 7010 7001 7002 7007 7011 7001 7002 7028 7032 7012 7001 7002 7028 7033 7013 7001 7002 7028 7034 7014 7001 7002 7028 7035 7015 7001 7002 7008 7018 7022 7016 7001 7002 7008 7018 7023 7017 7001 7002 7008 7018 7024 7018 7001 7002 7008 7019 7025 7019 7001 7002 7008 7019 7026 7020 7001 7003 7009 7021 7001 7003 7043 7022 7001 7003 7010 7020 7023 7001 7003 7010 7021 7024 7001 7003 7044 7045 7025 7001 7003 7044 7046 7026 7001 7003 7044 7047 7027 7001 7004 7013 7028 7001 7004 7040 7029 7001 7004 7011 7030 7001 7004 7038 7031 7001 7004 7012 7032 7001 7004 7039 7041 7033 7001 7004 7039 7042 7034 7001 7053 7049 7035 7001 7053 7050 7036 7001 7053 7048 7037 7038 7039 7040 7041 7042 7043 7044 7045 7046 7047 7048 7049 7050 7051 7052 7053 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need lookup help
The formula is referring to E1 which should be G2...Assuming you have your
employees codes in Col G, enter this formula in H2. =MIN(IF($A$1:$F$1000=G2,ROW($A$1:$F$1000),"")) and copy that down as required (**array entered**) If this post helps click Yes --------------- Jacob Skaria "Mike Coleman" wrote: 1 2 3 4 5 6 Employees Layer 7001 7005 7014 7001 1 7001 7005 7015 7051 7002 2 7001 7005 7015 7052 7003 2 7001 7005 7029 7004 2 7001 7005 7030 7005 2 7001 7005 7031 7036 7006 2 7001 7005 7031 7037 7007 2 7001 7002 7027 7008 2 7001 7002 7006 7017 7009 2 7001 7002 7006 7016 7010 2 7001 7002 7007 7011 2 7001 7002 7028 7032 7012 2 7001 7002 7028 7033 7013 2 7001 7002 7028 7034 7014 2 7001 7002 7028 7035 7015 2 7001 7002 7008 7018 7022 7016 2 7001 7002 7008 7018 7023 7017 17 7001 7002 7008 7018 7024 7018 18 7001 7002 7008 7019 7025 7019 19 7001 7002 7008 7019 7026 7020 20 7001 7003 7009 7021 21 7001 7003 7043 7022 2 7001 7003 7010 7020 7023 2 7001 7003 7010 7021 7024 2 7001 7003 7044 7045 7025 2 7001 7003 7044 7046 7026 2 7001 7003 7044 7047 7027 2 7001 7004 7013 7028 2 7001 7004 7040 7029 2 7001 7004 7011 7030 2 7001 7004 7038 7031 2 7001 7004 7012 7032 2 7001 7004 7039 7041 7033 2 7001 7004 7039 7042 7034 2 7001 7053 7049 7035 2 7001 7053 7050 7036 2 7001 7053 7048 7037 2 7038 2 7039 2 7040 2 7041 2 7042 2 7043 2 7044 2 7045 2 7046 2 7047 2 7048 2 7049 2 7050 2 7051 2 7052 2 7053 2 not Quiet working perfectly. this is what I am getting. For example 7006 should return a layer of 3. He is what is shown in the formula. =MIN(IF($A$1:$F$1000=E6,ROW($A$1:$F$1000),"")) "Jacob Skaria" wrote: Assuming you have your employees codes in in Col G, enter this formula in H2..which will return the row number =MIN(IF($A$1:$F$1000=E1,ROW($A$1:$F$1000),"")) and copy that down as required Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Mike Coleman" wrote: I need to lookup a value in a range based on a cell then return the value of the 1st row of the column the found cell is in. here's my wooksheet 1 2 3 4 5 6 Employees Layer 7001 7005 7014 7001 returns 1 7001 7005 7015 7051 7002 returns 2 7001 7005 7015 7052 7003 ect 7001 7005 7029 7004 ect 7001 7005 7030 7005 7001 7005 7031 7036 7006 7001 7005 7031 7037 7007 7001 7002 7027 7008 7001 7002 7006 7017 7009 7001 7002 7006 7016 7010 7001 7002 7007 7011 7001 7002 7028 7032 7012 7001 7002 7028 7033 7013 7001 7002 7028 7034 7014 7001 7002 7028 7035 7015 7001 7002 7008 7018 7022 7016 7001 7002 7008 7018 7023 7017 7001 7002 7008 7018 7024 7018 7001 7002 7008 7019 7025 7019 7001 7002 7008 7019 7026 7020 7001 7003 7009 7021 7001 7003 7043 7022 7001 7003 7010 7020 7023 7001 7003 7010 7021 7024 7001 7003 7044 7045 7025 7001 7003 7044 7046 7026 7001 7003 7044 7047 7027 7001 7004 7013 7028 7001 7004 7040 7029 7001 7004 7011 7030 7001 7004 7038 7031 7001 7004 7012 7032 7001 7004 7039 7041 7033 7001 7004 7039 7042 7034 7001 7053 7049 7035 7001 7053 7050 7036 7001 7053 7048 7037 7038 7039 7040 7041 7042 7043 7044 7045 7046 7047 7048 7049 7050 7051 7052 7053 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Linear Mode
