Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
When lookup_value corresponds several value in table array, it only
catchs the first value/cell with vlookup function, how to catch the second and more value? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
what do you want to do with em after they are caught?
You may be wanting to look at countif or sumif functions. -- Don Guillett SalesAid Software "Valley" wrote in message oups.com... When lookup_value corresponds several value in table array, it only catchs the first value/cell with vlookup function, how to catch the second and more value? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
One way - assuming your lookup table is in A1:B8, A14 contains your criteria,
enter in B14 (you must hold down Control+Shift keys while hitting Enter when you key these formulae in): =INDEX(B$1:B$8,SMALL(IF(A$1:A$8=A$14,ROW(INDIRECT( "1:"&ROWS(A$1:A$8))),""),ROWS(B$14:B14))) Copy down until you get an error. Or, if you don't like error messages, =IF(ROWS(B$14:B14)COUNTIF(A$1:A$8,A$14),"",INDEX( B$1:B$8,SMALL(IF(A$1:A$8=A$14,ROW(INDIRECT("1:"&RO WS(A$1:A$8))),""),ROWS(B$14:B14)))) "Valley" wrote: When lookup_value corresponds several value in table array, it only catchs the first value/cell with vlookup function, how to catch the second and more value? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
Valley wrote: When lookup_value corresponds several value in table array, it only catchs the first value/cell with vlookup function, how to catch the second and more value? My meaning is If the matching value is two or more, how to catch the second and the third and more. For example, Value is 16444050(in columnA1 and A2) in sheet1 There are 16444050(in column A1 and A2) and 22401241(in column B1) and 224016549(in column B2) in sheet2 When using vlookup, it can only catch B1 but not B2 of sheet2, how to catch B1 and B2, or how to catch B2 but not B1? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
See the "Arbitrary Lookups" section he
http://cpearson.com/excel/lookups.htm In article . com, "Valley" wrote: My meaning is If the matching value is two or more, how to catch the second and the third and more. For example, Value is 16444050(in columnA1 and A2) in sheet1 There are 16444050(in column A1 and A2) and 22401241(in column B1) and 224016549(in column B2) in sheet2 When using vlookup, it can only catch B1 but not B2 of sheet2, how to catch B1 and B2, or how to catch B2 but not B1? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
This function only can catch small or big, but
My meaning is Value is a column including many cells but not a cell, and several same data corresponds maybe several different data in each cell, and I need to catch all. The menioned below is only a sample. JE McGimpsey wrote: See the "Arbitrary Lookups" section he http://cpearson.com/excel/lookups.htm In article . com, "Valley" wrote: My meaning is If the matching value is two or more, how to catch the second and the third and more. For example, Value is 16444050(in columnA1 and A2) in sheet1 There are 16444050(in column A1 and A2) and 22401241(in column B1) and 224016549(in column B2) in sheet2 When using vlookup, it can only catch B1 but not B2 of sheet2, how to catch B1 and B2, or how to catch B2 but not B1? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
Hi Valley,
One way is to insert a new column in between columns A and B then into the top of the new (empty) column B paste and fill down this formula... =A1+(COUNTIF(A:A,A1)-COUNTIF(A1:A$65536,A1))*0.0001 This formula adds 0.0001*n to every succeeding recurrence of a column A value, where n is the number of previous recurrences. If the number of column A repetitions could exceed 10000 (as if!) you will have to change the small number,0.0001, to a suitable smaller value so that small number * greatest number of recurrences is less than 1. So, your first 16444050 remains that value in the new column B and doing a VLOOKUP based on the new column B will return 22401241. The second 16444050 in column A is the second occurrence of that value, so in the new column B it has been converted to 1644405.0001, so doing a VLOOKUP with 16444405.0001 will return 224016549. The next (3rd) occurrence of 16444050 would be converted to 16444050.0002 and this value should be used when trying to find the VLOOKUP value in column B. One way of setting up the VLOOKUP is, assuming C1:E2 are available... In C1 the text "Column A Value" In C2, type in the column A value for the VLOOKUP to process In D1 the text "Occurrence" In D2, type in which occurrence. This is an integer between (and including) 1 and the total number of occurences for that column A value. If you exceed the total number the VLOOKUP just returns its error result. In E1 the text "Column B Value" In E2 the VLOOKUP formula... =VLOOKUP(C2 + (D2-1)*0.0001,B1:C65536,2,FALSE) you might want to change the C65536 part of the address to indicate the number of rows occupied by your data. Hope this makes sense! This is the way I have overcome the problem of recurring values in a VLOOKUP table. Ken Johnson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
Hi Johnson,
Thanks very much! This surely is a good way. BR Valley Ken Johnson wrote: Hi Valley, One way is to insert a new column in between columns A and B then into the top of the new (empty) column B paste and fill down this formula... =A1+(COUNTIF(A:A,A1)-COUNTIF(A1:A$65536,A1))*0.0001 This formula adds 0.0001*n to every succeeding recurrence of a column A value, where n is the number of previous recurrences. If the number of column A repetitions could exceed 10000 (as if!) you will have to change the small number,0.0001, to a suitable smaller value so that small number * greatest number of recurrences is less than 1. So, your first 16444050 remains that value in the new column B and doing a VLOOKUP based on the new column B will return 22401241. The second 16444050 in column A is the second occurrence of that value, so in the new column B it has been converted to 1644405.0001, so doing a VLOOKUP with 16444405.0001 will return 224016549. The next (3rd) occurrence of 16444050 would be converted to 16444050.0002 and this value should be used when trying to find the VLOOKUP value in column B. One way of setting up the VLOOKUP is, assuming C1:E2 are available... In C1 the text "Column A Value" In C2, type in the column A value for the VLOOKUP to process In D1 the text "Occurrence" In D2, type in which occurrence. This is an integer between (and including) 1 and the total number of occurences for that column A value. If you exceed the total number the VLOOKUP just returns its error result. In E1 the text "Column B Value" In E2 the VLOOKUP formula... =VLOOKUP(C2 + (D2-1)*0.0001,B1:C65536,2,FALSE) you might want to change the C65536 part of the address to indicate the number of rows occupied by your data. Hope this makes sense! This is the way I have overcome the problem of recurring values in a VLOOKUP table. Ken Johnson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to catch the second or more cell with vlookup
Hi Valley,
You're welcome. Thanks for the feedback. JMB's solution and Chip Pearson's Arbitrary Lookup both work. Perhaps you weren't using Control + Shift + Enter when entering their formulas. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Cell Validation List & Linked Cell VLOOKUP | Excel Discussion (Misc queries) | |||
VLOOKUP using a cell calculated with NOW returns Error | Excel Worksheet Functions | |||
Convert VLOOKUP to absolute cell reference | Excel Discussion (Misc queries) | |||
VLookup to sum cell values | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |