|
| 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
May 16th, 2009, 05:28 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Help using VLOOKUP command
I have 144 unique data fields in cells B5:B148. Each of these data fields is
individually located somewhere (not in order) in cells K5:AH148. What I want to do is in C5, find the value in B5 somewhere in the K5:AH148 range, and return the value in the very next column to the right. For instance, the matching B5 data could be in cell S57 for example, and I'd want to display the data in T57 in cell C5. Any idea as to how to do this? Thanks. 
|
|
#2
May 16th, 2009, 05:48 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using VLOOKUP command
In cell B5 enter the data to be searched
C5 enter the below formula 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 the Formula Bar you can notice the curly braces at both ends "{=formula}" =INDEX($K$5:$AH$145,MIN(IF($K$5:$AH$145=B5,ROW($K$ 5:$AH$145),""))-ROW($K$5:$AH$145)+1,MIN(IF($K$5:$AH$145=B5,COLUMN( $K$5:$AH$145),""))-COLUMN($K$5:$AH$145)+2) If this post helps click Yes --------------- Jacob Skaria "Scott" wrote: I have 144 unique data fields in cells B5:B148. Each of these data fields is individually located somewhere (not in order) in cells K5:AH148. What I want to do is in C5, find the value in B5 somewhere in the K5:AH148 range, and return the value in the very next column to the right. For instance, the matching B5 data could be in cell S57 for example, and I'd want to display the data in T57 in cell C5. Any idea as to how to do this? Thanks.
|
|
#3
May 16th, 2009, 05:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using VLOOKUP command
A simpler one..Again please note that this is an array formula
=INDIRECT(ADDRESS(MIN(IF($K$5:$O$20=B7,ROW($K$5:$O $20),"")),MIN(IF($K$5:$O$20=B7,COLUMN($K$5:$O$20), ""))+1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In cell B5 enter the data to be searched C5 enter the below formula 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 the Formula Bar you can notice the curly braces at both ends "{=formula}" =INDEX($K$5:$AH$145,MIN(IF($K$5:$AH$145=B5,ROW($K$ 5:$AH$145),""))-ROW($K$5:$AH$145)+1,MIN(IF($K$5:$AH$145=B5,COLUMN( $K$5:$AH$145),""))-COLUMN($K$5:$AH$145)+2) If this post helps click Yes --------------- Jacob Skaria "Scott" wrote: I have 144 unique data fields in cells B5:B148. Each of these data fields is individually located somewhere (not in order) in cells K5:AH148. What I want to do is in C5, find the value in B5 somewhere in the K5:AH148 range, and return the value in the very next column to the right. For instance, the matching B5 data could be in cell S57 for example, and I'd want to display the data in T57 in cell C5. Any idea as to how to do this? Thanks.
|
|
#4
May 16th, 2009, 06:25 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using VLOOKUP command
Very nice.
"Jacob Skaria" wrote: A simpler one..Again please note that this is an array formula =INDIRECT(ADDRESS(MIN(IF($K$5:$O$20=B7,ROW($K$5:$O $20),"")),MIN(IF($K$5:$O$20=B7,COLUMN($K$5:$O$20), ""))+1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In cell B5 enter the data to be searched C5 enter the below formula 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 the Formula Bar you can notice the curly braces at both ends "{=formula}" =INDEX($K$5:$AH$145,MIN(IF($K$5:$AH$145=B5,ROW($K$ 5:$AH$145),""))-ROW($K$5:$AH$145)+1,MIN(IF($K$5:$AH$145=B5,COLUMN( $K$5:$AH$145),""))-COLUMN($K$5:$AH$145)+2) If this post helps click Yes --------------- Jacob Skaria "Scott" wrote: I have 144 unique data fields in cells B5:B148. Each of these data fields is individually located somewhere (not in order) in cells K5:AH148. What I want to do is in C5, find the value in B5 somewhere in the K5:AH148 range, and return the value in the very next column to the right. For instance, the matching B5 data could be in cell S57 for example, and I'd want to display the data in T57 in cell C5. Any idea as to how to do this? Thanks.
|
|
#5
May 18th, 2009, 03:04 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using VLOOKUP command
This is returning a #VALUE! error.
"Sheeloo" wrote: Very nice. "Jacob Skaria" wrote: A simpler one..Again please note that this is an array formula =INDIRECT(ADDRESS(MIN(IF($K$5:$O$20=B7,ROW($K$5:$O $20),"")),MIN(IF($K$5:$O$20=B7,COLUMN($K$5:$O$20), ""))+1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In cell B5 enter the data to be searched C5 enter the below formula 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 the Formula Bar you can notice the curly braces at both ends "{=formula}" =INDEX($K$5:$AH$145,MIN(IF($K$5:$AH$145=B5,ROW($K$ 5:$AH$145),""))-ROW($K$5:$AH$145)+1,MIN(IF($K$5:$AH$145=B5,COLUMN( $K$5:$AH$145),""))-COLUMN($K$5:$AH$145)+2) If this post helps click Yes --------------- Jacob Skaria "Scott" wrote: I have 144 unique data fields in cells B5:B148. Each of these data fields is individually located somewhere (not in order) in cells K5:AH148. What I want to do is in C5, find the value in B5 somewhere in the K5:AH148 range, and return the value in the very next column to the right. For instance, the matching B5 data could be in cell S57 for example, and I'd want to display the data in T57 in cell C5. Any idea as to how to do this? Thanks.
|
|
#6
May 18th, 2009, 04:14 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using VLOOKUP command
Did you press CTRL-SHIFT-ENTER together after typing/pasting the formula? "Scott" wrote: This is returning a #VALUE! error. "Sheeloo" wrote: Very nice. "Jacob Skaria" wrote: A simpler one..Again please note that this is an array formula =INDIRECT(ADDRESS(MIN(IF($K$5:$O$20=B7,ROW($K$5:$O $20),"")),MIN(IF($K$5:$O$20=B7,COLUMN($K$5:$O$20), ""))+1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In cell B5 enter the data to be searched C5 enter the below formula 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 the Formula Bar you can notice the curly braces at both ends "{=formula}" =INDEX($K$5:$AH$145,MIN(IF($K$5:$AH$145=B5,ROW($K$ 5:$AH$145),""))-ROW($K$5:$AH$145)+1,MIN(IF($K$5:$AH$145=B5,COLUMN( $K$5:$AH$145),""))-COLUMN($K$5:$AH$145)+2) If this post helps click Yes --------------- Jacob Skaria "Scott" wrote: I have 144 unique data fields in cells B5:B148. Each of these data fields is individually located somewhere (not in order) in cells K5:AH148. What I want to do is in C5, find the value in B5 somewhere in the K5:AH148 range, and return the value in the very next column to the right. For instance, the matching B5 data could be in cell S57 for example, and I'd want to display the data in T57 in cell C5. Any idea as to how to do this? Thanks.
|
|
#7
May 18th, 2009, 11:44 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using VLOOKUP command
Dear Scott
If you have used the second formula; the range was wrong..Try the below =INDIRECT(ADDRESS(MIN(IF($K$5:$AH$145=B5,ROW($K$5: $AH$145),"")),MIN(IF($K$5:$AH$145=B5,COLUMN($K$5:$ AH$145),""))+1)) 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 "Scott" wrote: This is returning a #VALUE! error. "Sheeloo" wrote: Very nice. "Jacob Skaria" wrote: A simpler one..Again please note that this is an array formula =INDIRECT(ADDRESS(MIN(IF($K$5:$O$20=B7,ROW($K$5:$O $20),"")),MIN(IF($K$5:$O$20=B7,COLUMN($K$5:$O$20), ""))+1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In cell B5 enter the data to be searched C5 enter the below formula 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 the Formula Bar you can notice the curly braces at both ends "{=formula}" =INDEX($K$5:$AH$145,MIN(IF($K$5:$AH$145=B5,ROW($K$ 5:$AH$145),""))-ROW($K$5:$AH$145)+1,MIN(IF($K$5:$AH$145=B5,COLUMN( $K$5:$AH$145),""))-COLUMN($K$5:$AH$145)+2) If this post helps click Yes --------------- Jacob Skaria "Scott" wrote: I have 144 unique data fields in cells B5:B148. Each of these data fields is individually located somewhere (not in order) in cells K5:AH148. What I want to do is in C5, find the value in B5 somewhere in the K5:AH148 range, and return the value in the very next column to the right. For instance, the matching B5 data could be in cell S57 for example, and I'd want to display the data in T57 in cell C5. Any idea as to how to do this? Thanks.
|
|
#8
May 18th, 2009, 11:45 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using VLOOKUP command
Thanks Sheeloo...
If this post helps click Yes --------------- Jacob Skaria "Sheeloo" wrote: Very nice. "Jacob Skaria" wrote: A simpler one..Again please note that this is an array formula =INDIRECT(ADDRESS(MIN(IF($K$5:$O$20=B7,ROW($K$5:$O $20),"")),MIN(IF($K$5:$O$20=B7,COLUMN($K$5:$O$20), ""))+1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In cell B5 enter the data to be searched C5 enter the below formula 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 the Formula Bar you can notice the curly braces at both ends "{=formula}" =INDEX($K$5:$AH$145,MIN(IF($K$5:$AH$145=B5,ROW($K$ 5:$AH$145),""))-ROW($K$5:$AH$145)+1,MIN(IF($K$5:$AH$145=B5,COLUMN( $K$5:$AH$145),""))-COLUMN($K$5:$AH$145)+2) If this post helps click Yes --------------- Jacob Skaria "Scott" wrote: I have 144 unique data fields in cells B5:B148. Each of these data fields is individually located somewhere (not in order) in cells K5:AH148. What I want to do is in C5, find the value in B5 somewhere in the K5:AH148 range, and return the value in the very next column to the right. For instance, the matching B5 data could be in cell S57 for example, and I'd want to display the data in T57 in cell C5. Any idea as to how to do this? Thanks.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
