Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup using 2 criteria
I am trying to use vlookup to populate a table with less data over the same
time period. I can use the data/time column (see below) but the date/time repeats sometimes for different values in the second column. So my question is how can i get vlookup to match the values in the first and second columns when it retrieves data for the column i am interested in... 7/1/2008 0:52 KBA 25.6 (column of interest) .. .. different time and date stamps... .. .. 7/1/2008 0:52 Kgg 30.6 When i use vlookup now to find the exact match it returns the first instance on that particular time stamp, how can i get it to match to that second column too. Many thanks!! Marcus |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup using 2 criteria
Concatenate the first and second columns in a third column then use vlookup
on the concatenated value. Tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup using 2 criteria
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) mebsmith wrote: I am trying to use vlookup to populate a table with less data over the same time period. I can use the data/time column (see below) but the date/time repeats sometimes for different values in the second column. So my question is how can i get vlookup to match the values in the first and second columns when it retrieves data for the column i am interested in... 7/1/2008 0:52 KBA 25.6 (column of interest) . . different time and date stamps... . . 7/1/2008 0:52 Kgg 30.6 When i use vlookup now to find the exact match it returns the first instance on that particular time stamp, how can i get it to match to that second column too. Many thanks!! Marcus -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with 4 Criteria | Excel Discussion (Misc queries) | |||
vlookup 2 criteria | Excel Discussion (Misc queries) | |||
Vlookup with more than 1 criteria | Excel Discussion (Misc queries) | |||
VLOOKUP or IF with many criteria | Excel Worksheet Functions | |||
VLOOKUP with MAX criteria | Excel Discussion (Misc queries) |