Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with a vlookup but returning a particular match?
vlookup e.g.
Line 1 (Table)(Col A)REF123(ColB)245 Line 2 (Table)(Col A)REF123(ColB)246 Line 3 (Table)(Col A)REF123(ColB)247 and so on.. On column C(above) i need to return column b from the array.(below) Line 1 (Array)(Col A)REF122(ColB)243 Line 2 (Array)(Col A)REF123(ColB)244 Line 3 (Array)(Col A)REF123(ColB)245 when i do a vlookup i have to use column A for the data but when i ask for column b to be returned it dosent match (Line 1 on Table pulls through line 2 on array when i need line 3 which is the correct match. Is it possible to do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with a vlookup but returning a particular match?
How would it know, it returns the first match.
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "D7ONO" wrote in message ... vlookup e.g. Line 1 (Table)(Col A)REF123(ColB)245 Line 2 (Table)(Col A)REF123(ColB)246 Line 3 (Table)(Col A)REF123(ColB)247 and so on.. On column C(above) i need to return column b from the array.(below) Line 1 (Array)(Col A)REF122(ColB)243 Line 2 (Array)(Col A)REF123(ColB)244 Line 3 (Array)(Col A)REF123(ColB)245 when i do a vlookup i have to use column A for the data but when i ask for column b to be returned it dosent match (Line 1 on Table pulls through line 2 on array when i need line 3 which is the correct match. Is it possible to do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with a vlookup but returning a particular match?
Hi, As Bob has said, vlookup isn't that smart - & I may be slightly off track here, but why is line 3 the correct match? If it's b/c this row (ie column A value & column B value) is identical in both the table & the array, what is the point of returning column B (of array) in column C of the Table (you could just type"=B1")? If your intention is to return a value from an identical row you need to use a concatenated helper column (eg, cell C1 contains "=A1&"%"&B1") for your lookup/matching or a sum product formula. Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=539199 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with a vlookup but returning a particular match?
with the data i have i firstly have to do the lookup by using column a to
match the data however columns b also have to match as they can be different which is why line 3 is correct (colB is 245 on both sheets) whereas line 2 on the array column b is 244 this is why i need a formula to search on column b after the initial lookup. hope this makes sense "broro183" wrote: Hi, As Bob has said, vlookup isn't that smart - & I may be slightly off track here, but why is line 3 the correct match? If it's b/c this row (ie column A value & column B value) is identical in both the table & the array, what is the point of returning column B (of array) in column C of the Table (you could just type"=B1")? If your intention is to return a value from an identical row you need to use a concatenated helper column (eg, cell C1 contains "=A1&"%"&B1") for your lookup/matching or a sum product formula. Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=539199 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with a vlookup but returning a particular match?
Seems odd that you want to find a value that you already have?
=INDEX(Table!B1:B100,MATCH(1,(Table!A1:A100=Array! A1)*(Table(B1:B100=Array:B 1),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "D7ONO" wrote in message ... with the data i have i firstly have to do the lookup by using column a to match the data however columns b also have to match as they can be different which is why line 3 is correct (colB is 245 on both sheets) whereas line 2 on the array column b is 244 this is why i need a formula to search on column b after the initial lookup. hope this makes sense "broro183" wrote: Hi, As Bob has said, vlookup isn't that smart - & I may be slightly off track here, but why is line 3 the correct match? If it's b/c this row (ie column A value & column B value) is identical in both the table & the array, what is the point of returning column B (of array) in column C of the Table (you could just type"=B1")? If your intention is to return a value from an identical row you need to use a concatenated helper column (eg, cell C1 contains "=A1&"%"&B1") for your lookup/matching or a sum product formula. Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=539199 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP returning wrong row | Excel Worksheet Functions | |||
is there a way to search with vlookup to match more than 1 column | Excel Discussion (Misc queries) | |||
VLOOKUP returning LAST match | Excel Discussion (Misc queries) | |||
troubleshoot vlookup returning #N/A | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |