| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to sum multiple matches to a vlookup command | Excel Worksheet Functions | |||
| Vlookup command not giving the correct result | Excel Worksheet Functions | |||
| VLOOKUP JUST RETURNS THE TEXT OF THE COMMAND | Excel Worksheet Functions | |||
| using a vlookup command in a for next loop in a macro in excel | Excel Discussion (Misc queries) | |||
| Using the VLOOKUP Command | Excel Worksheet Functions | |||
Linear Mode
