Formula Question

  • Thread starter Thread starter AKS
  • Start date Start date
A

AKS

I have two separate sheets. One sheet contains a person's name with their
pets name and an attached value (3 separate columns). In the other sheet I
have the same person's name with their pets. In the value column of the
second sheet I want it to look up the exact person's name and pet match and
give me the attached value. Anyone have any ideas. I have tried several
different things with no luck.

Thanks!
 
=INDEX(Sheet2!C:C,MATCH(1,(Sheet2!A1:$A$200=$A$2)*(Sheet2!$B$1:$B$200=B2),0))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.
 
You can concatenate the person's name and their pet name in both sheets and
then do a VLOOKUP based on the concatenated value. In the sheet with the
values, you'd have to set up a field were both names are already joined, so
assuming your concatenated name is column C and your 3 values are columns,
D:F, it would look something like this:

VLOOKUP(CONCATENATE(name, pet name), Other sheet!C:F, 2, FALSE)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Consolidation formula 1
Use Vlookup to store formulas? 6
Function neede which checks if a value in a cell is in a defined s 3
Vlookup and return sheet name also 2
Hacking suprise, so beef up your protection 0
Adding up total hours worked on employee schedule using various criteria 0
Excel Is this possible?? 3
Dependent drop down dynamic range formula, off sheet. 7

Back
Top