| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or MATCH or Magical function
Hello,
I am running into a problem and I am not sure which function to use. Suppose I have a worksheet1 with of data i.e. the person and the brand of cars they own. Person Car A BMW A Ford B GM B Mercedes B Porsche C Honda D Toyota D Lexus Is it possible to create a second worksheet ~ say worksheet 2 and depending on the data from worksheet 1, it would fill out Y, N Person Car Own (Y/N) A BMW Y A Ford Y A GM N A Mercedes N A Porsche N A Honda N A Toyota N A Lexus N Thank you for your time! PJS |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP or MATCH or Magical function
Assuming you want Y/N in Col I and values to be looked up are in Col F & G
(second set of your data) Enter this where you want Y/N =IF((VLOOKUP(F2,A:B,2,FALSE)=G2),"Y","N") This assumes your first set of data is in Col A & B... It does not check for errrors. You may like to wrap the above in a ISNA formula... "PJS" wrote: Hello, I am running into a problem and I am not sure which function to use. Suppose I have a worksheet1 with of data i.e. the person and the brand of cars they own. Person Car A BMW A Ford B GM B Mercedes B Porsche C Honda D Toyota D Lexus Is it possible to create a second worksheet ~ say worksheet 2 and depending on the data from worksheet 1, it would fill out Y, N Person Car Own (Y/N) A BMW Y A Ford Y A GM N A Mercedes N A Porsche N A Honda N A Toyota N A Lexus N Thank you for your time! PJS |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP or MATCH or Magical function
Sorry, my solution will not work...
I did not consider multiple occurrences in Col A... "PJS" wrote: Hello, I am running into a problem and I am not sure which function to use. Suppose I have a worksheet1 with of data i.e. the person and the brand of cars they own. Person Car A BMW A Ford B GM B Mercedes B Porsche C Honda D Toyota D Lexus Is it possible to create a second worksheet ~ say worksheet 2 and depending on the data from worksheet 1, it would fill out Y, N Person Car Own (Y/N) A BMW Y A Ford Y A GM N A Mercedes N A Porsche N A Honda N A Toyota N A Lexus N Thank you for your time! PJS |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP or MATCH or Magical function
One way is to combine col A & B in a helper column C (=A&B) and then use this
=IF((VLOOKUP(F2&G2,C:C,1,FALSE)=G2),"Y","N") "Sheeloo" wrote: Assuming you want Y/N in Col I and values to be looked up are in Col F & G (second set of your data) Enter this where you want Y/N =IF((VLOOKUP(F2,A:B,2,FALSE)=G2),"Y","N") This assumes your first set of data is in Col A & B... It does not check for errrors. You may like to wrap the above in a ISNA formula... "PJS" wrote: Hello, I am running into a problem and I am not sure which function to use. Suppose I have a worksheet1 with of data i.e. the person and the brand of cars they own. Person Car A BMW A Ford B GM B Mercedes B Porsche C Honda D Toyota D Lexus Is it possible to create a second worksheet ~ say worksheet 2 and depending on the data from worksheet 1, it would fill out Y, N Person Car Own (Y/N) A BMW Y A Ford Y A GM N A Mercedes N A Porsche N A Honda N A Toyota N A Lexus N Thank you for your time! PJS |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP or MATCH or Magical function
=if(sumproduct(--(Sheet1!$A$1:$A$100=A2),--(Sheet1!$B$1:$B$100)=B2)=0,"N","Y")
Enter that in Sheet2!C2 and copy it down to fill out your table on Sheet2. The 100s can be changed to the final row of your data on Sheet1. "PJS" wrote: Hello, I am running into a problem and I am not sure which function to use. Suppose I have a worksheet1 with of data i.e. the person and the brand of cars they own. Person Car A BMW A Ford B GM B Mercedes B Porsche C Honda D Toyota D Lexus Is it possible to create a second worksheet ~ say worksheet 2 and depending on the data from worksheet 1, it would fill out Y, N Person Car Own (Y/N) A BMW Y A Ford Y A GM N A Mercedes N A Porsche N A Honda N A Toyota N A Lexus N Thank you for your time! PJS |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP or MATCH or Magical function
=IF(ISNUMBER(MATCH(1,(Sheet1!A1:A10=A1)*(Sheet1!B1 :B10=B1),0)),"yes","no")
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 ranges to match--but you can't use whole columns (except in xl2007). Assumes that the table is on Sheet1 in A1:B10 and that the values to match are in A1 and B1 of a different sheet. PJS wrote: Hello, I am running into a problem and I am not sure which function to use. Suppose I have a worksheet1 with of data i.e. the person and the brand of cars they own. Person Car A BMW A Ford B GM B Mercedes B Porsche C Honda D Toyota D Lexus Is it possible to create a second worksheet ~ say worksheet 2 and depending on the data from worksheet 1, it would fill out Y, N Person Car Own (Y/N) A BMW Y A Ford Y A GM N A Mercedes N A Porsche N A Honda N A Toyota N A Lexus N Thank you for your time! PJS -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP or MATCH or Magical function
Hi,
The answer may be yes, but without you telling us what condition will determine whether to display yes or no we are all just taking a guess at what formula you need is. Look at the different answers you have recieved, each is a shot in the dark. Please provide us with more information. -- Thanks, Shane Devenshire "PJS" wrote: Hello, I am running into a problem and I am not sure which function to use. Suppose I have a worksheet1 with of data i.e. the person and the brand of cars they own. Person Car A BMW A Ford B GM B Mercedes B Porsche C Honda D Toyota D Lexus Is it possible to create a second worksheet ~ say worksheet 2 and depending on the data from worksheet 1, it would fill out Y, N Person Car Own (Y/N) A BMW Y A Ford Y A GM N A Mercedes N A Porsche N A Honda N A Toyota N A Lexus N Thank you for your time! PJS |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP or MATCH or Magical function
Try also a pivot table. It'll deliver a 99.99% close fit results in a matter
of seconds with a few clicks, drags n drops .. Some easy steps to lead you in (Excel 2003): Select any cell within the source table Click Data Pivot table Click Next Next In step 3 of the wiz, click Layout: Drag n drop "Person" in ROW area Double-click on it, set Subtotals to None Drag n drop "Car" in ROW area Double-click on it, check "Show items with no data" Drag n drop "Car" in DATA area (it'll appear as Count) Click OK Finish. That's it! Hop over to the pivot sheet (to the left) for the results -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:57 xdemechanik --- "PJS" wrote: I am running into a problem and I am not sure which function to use. Suppose I have a worksheet1 with of data i.e. the person and the brand of cars they own. Person Car A BMW A Ford B GM B Mercedes B Porsche C Honda D Toyota D Lexus Is it possible to create a second worksheet ~ say worksheet 2 and depending on the data from worksheet 1, it would fill out Y, N Person Car Own (Y/N) A BMW Y A Ford Y A GM N A Mercedes N A Porsche N A Honda N A Toyota N A Lexus N |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Wow Thank you for the suggestions!
Thank you everyone, I will try the different formulas and see which one works
the best. Thanks again!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup or match function? | Excel Worksheet Functions | |||
| Vlookup/Match Function | Excel Worksheet Functions | |||
| vlookup/ match or other function?? | Excel Worksheet Functions | |||
| Magical disappearing shapes! | Excel Discussion (Misc queries) | |||
| MATCH function in a VLOOKUP | Excel Worksheet Functions | |||
Linear Mode
