Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Three columns and returning a value
I have a Spreadsheet with two sheets.
Sheet 1 has the following data in columns Forname Surname Birth Place Nationality Alan Jones Liverpool Allan Jones Cardiff David Ellis Manchester David Ellis Leeds Clive Newman London Sarah Roberts Cardiff Sheet 2 has the following Surname Forename Birthplace Nationality Forename Birthplace Natioanlity Jones Alan Liverpool English Allan Cardiff Welsh Ellis David Manchester English David Leeds English etc How can I insert in sheet 1 the nationality where all three varialble must match i.e. Alan Jones Liverpool = Jones Alan Liverpool and returns the text English in Sheet 1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Three columns and returning a value
You could insert a new column D in Sheet2 and fill it with this
formula: =A2&B2&C2 and copy this down. Similarly, insert a new column H and put this formula in H2: =A2&F2&G2 and copy this down. Then in Sheet1 D2 you can put this formula: =IF(ISNA(VLOOKUP(B2&A2&C2,Sheet2!D:E, 2,0)),IF(ISNA(VLOOKUP(B2&A2&C2,Sheet2!H:I,2,0)),"n ot present",VLOOKUP(B2&A2&C2,Sheet2!H:I,2,0)),VLOOKUP (B2&A2&C2,Sheet2!D:E, 2,0)) Copy this down as required. Hope this helps. Pete On Apr 28, 1:33*pm, Rob wrote: I have a Spreadsheet with two sheets. Sheet 1 has the following data in columns Forname * * Surname * * *Birth Place * * * Nationality Alan * * * * * * Jones * * * * * Liverpool Allan * * * * * *Jones * * * * * Cardiff David * * * * * *Ellis * * * * * * Manchester David * * * * * *Ellis * * * * * * Leeds Clive * * * * * * Newman * * *London Sarah * * * * * Roberts * * * *Cardiff Sheet 2 has the following Surname * * *Forename * *Birthplace * *Nationality Forename Birthplace * Natioanlity Jones * * * * * Alan * * * * * * Liverpool * * *English * * Allan * * * * Cardiff * * * Welsh Ellis * * * * * * *David * * * * * Manchester *English * * David * * * * Leeds * * * *English etc How can I insert in sheet 1 the nationality where all three varialble must match i.e. Alan Jones Liverpool = Jones Alan Liverpool and returns the text English in Sheet 1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Three columns and returning a value
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)) Rob wrote: I have a Spreadsheet with two sheets. Sheet 1 has the following data in columns Forname Surname Birth Place Nationality Alan Jones Liverpool Allan Jones Cardiff David Ellis Manchester David Ellis Leeds Clive Newman London Sarah Roberts Cardiff Sheet 2 has the following Surname Forename Birthplace Nationality Forename Birthplace Natioanlity Jones Alan Liverpool English Allan Cardiff Welsh Ellis David Manchester English David Leeds English etc How can I insert in sheet 1 the nationality where all three varialble must match i.e. Alan Jones Liverpool = Jones Alan Liverpool and returns the text English in Sheet 1 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Three columns and returning a value
Thank you tried this and it worked
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Three columns and returning a value
Thank you tried this one and it worked
"Dave Peterson" wrote: |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Three columns and returning a value
You're welcome - thanks for feeding back.
Pete On Apr 29, 8:50*am, Rob wrote: Thank you tried this and it worked |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching & Returning values | Excel Discussion (Misc queries) | |||
only returning 1st matching entry in table, need to find next matc | Excel Worksheet Functions | |||
Matching numbers in an Array and returning values for matched numb | Excel Discussion (Misc queries) | |||
returning all matching values in column A that have the same value for columnB | Excel Worksheet Functions | |||
Returning a Value by Matching Two Columns of Data | Excel Worksheet Functions |