Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP MULTIPLE VALUES
Hi,
If anyone can help with this, I'd be really grateful) I have 3 columns with data: The first, has a list of reference numbers (i.e. 00563124P) which occure more than once, the second has a list of text values and the third has a list of text values. I want to use two reference values: The first will equate to one of the ref numbers in the first column, the second will equate to one of the text values in the second. I'm trying to return the value from the equivalent row in the third column where the reference values match the data (on the same row) for the first and second columns. A traditional lookup won't do it and I'm a bit stuck... Cheers, Ed. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP MULTIPLE VALUES
For Vlookup, it's a limitation that you can only rely on one reference.
In such case, I suggest you to create a combined field between col 2 and col 3 so you can lookup from the combined field. e.g. ColA ColB ColC 001 Ken 20 002 Son 50 001 Pet 90 002 Bol 65 combined into 2 cols to lookup: ColC ColD 001Ken 20 002Son 50 001Pet 90 002Bol 65 hope this help. "Ed" wrote: Hi, If anyone can help with this, I'd be really grateful) I have 3 columns with data: The first, has a list of reference numbers (i.e. 00563124P) which occure more than once, the second has a list of text values and the third has a list of text values. I want to use two reference values: The first will equate to one of the ref numbers in the first column, the second will equate to one of the text values in the second. I'm trying to return the value from the equivalent row in the third column where the reference values match the data (on the same row) for the first and second columns. A traditional lookup won't do it and I'm a bit stuck... Cheers, Ed. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP MULTIPLE VALUES
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)) Ed wrote: Hi, If anyone can help with this, I'd be really grateful) I have 3 columns with data: The first, has a list of reference numbers (i.e. 00563124P) which occure more than once, the second has a list of text values and the third has a list of text values. I want to use two reference values: The first will equate to one of the ref numbers in the first column, the second will equate to one of the text values in the second. I'm trying to return the value from the equivalent row in the third column where the reference values match the data (on the same row) for the first and second columns. A traditional lookup won't do it and I'm a bit stuck... Cheers, Ed. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup on multiple values | Excel Discussion (Misc queries) | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
lookup multiple values | Excel Worksheet Functions | |||
how do I lookup multiple values | Excel Discussion (Misc queries) | |||
Multiple lookup values in =HLOOKUP | Excel Discussion (Misc queries) |