Complex or Deadly Difficult/Impossible??? : Multiple argument lookups/matches between 2 sheets

R

RichieA

New Member
Joined
Aug 20, 2015
Messages
16
I say Deadly - because after a week on and off of trying to work this out I'm still none the wiser and I have brain freeze! :confused: I'm not super slick on Excel but I thought this would be possible. In Sheet 1, I'm trying to populate Column C with the corresponding value in Sheet 2. As I write this, it sounds really simple, but I fear that it isn't. :mad:

So the logic for the value I want returned in Column C in Sheet1 1is something like If A2=Sheet2A2:A6 AND B2=Sheet2B1:B5 then return value in Sheet2 CellB2:F6

Is that possible? And if so is it Nesteds IFs or is it a more complex VLookup?

I can't simply transpose one of the sheets because the info I want is part of a much bigger sheet. The below is just a truncated simplified view for demonstration of the problem.

Any help would be MUCH appreciated!!

Cheers

RichieA


[TABLE="width: 530"]
<tbody>[TR]
[TD="class: xl417, width: 184, bgcolor: transparent"]Sheet 1
[/TD]
[TD="width: 116, bgcolor: transparent"][/TD]
[TD="width: 101, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 114, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl412, bgcolor: #CCC0DA"]Client
[/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Office
[/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Value
[/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Next Action
[/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Latest Event
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl413, bgcolor: #F2F2F2"]Austria
[/TD]
[TD="class: xl415, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Arrange meet
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Sold 1000 units
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl414, width: 116, bgcolor: #F2F2F2"]Belgium
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Supply goods
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Visit complete
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl414, width: 116, bgcolor: #F2F2F2"]Canada
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Supply goods
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Visit complete
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl414, width: 116, bgcolor: #F2F2F2"]Denmark
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Shut down
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Shut down
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl414, width: 116, bgcolor: #F2F2F2"]Egypt
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl413, bgcolor: #F2F2F2"]Austria
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl414, width: 116, bgcolor: #F2F2F2"]Belgium
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl414, width: 116, bgcolor: #F2F2F2"]Canada
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Rugby
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Lunch
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl414, width: 116, bgcolor: #F2F2F2"]Denmark
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl414, width: 116, bgcolor: #F2F2F2"]Egypt
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl417, bgcolor: transparent"]Sheet 2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Austria
[/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Belgium
[/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Canada
[/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Denmark
[/TD]
[TD="class: xl412, bgcolor: #CCC0DA"]Egypt
[/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]63
[/TD]
[TD="bgcolor: transparent, align: right"]564
[/TD]
[TD="bgcolor: transparent, align: right"]34
[/TD]
[TD="bgcolor: transparent, align: right"]67
[/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]32
[/TD]
[TD="bgcolor: transparent, align: right"]56
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Charlie Inc
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent, align: right"]29
[/TD]
[TD="bgcolor: transparent, align: right"]45
[/TD]
[TD="bgcolor: transparent, align: right"]61
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Delta Inc
[/TD]
[TD="bgcolor: transparent, align: right"]33
[/TD]
[TD="bgcolor: transparent, align: right"]568
[/TD]
[TD="bgcolor: transparent, align: right"]98
[/TD]
[TD="bgcolor: transparent, align: right"]52
[/TD]
[TD="bgcolor: transparent, align: right"]231
[/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #F2F2F2"]Echo Inc
[/TD]
[TD="bgcolor: transparent, align: right"]97
[/TD]
[TD="bgcolor: transparent, align: right"]238
[/TD]
[TD="bgcolor: transparent, align: right"]56
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]47
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Welcome to Mr Excel

Try this in Sheet1 C2
=INDEX(Sheet2!$B$2:$F$6,MATCH($A2,Sheet2!$A$2:$A$6,0),MATCH($B2,Sheet2!$B$1:$F$1,0))
copy down

Hope this helps

M.
 
Upvote 0
Wow - thanks for such a speedy response Marcelo - wasn't expecting that. Not knowledgable of the Index function. I will pick this apart to apply it to the live sheets and let you know how I get on.

Thank you
 
Upvote 0
Marcelo - that works perfectly, thank you so much for your help. I can now save on headache pills! I'd call you a genius but that would give away my basic knowledge of Excel as to you this was probably simple!!! I'll read up on that and try and understand the logic as i think I will need a different iteration of this again.

One follow up, then - in this example, Sheet 2 is my master source of clients and values and Sheet 1 I am building from scratch as a sort of Account Plan/Summary "database" with actions (in the absence of a company CRM system). You can see from Sheet 2 that there is One row entry per Client name of which there are 600+. In Sheet 1, I need to reflect these but need circa 14 row entries per client. Is there an easy way of automating this with a formula? Macros I know NOTHING about! So I could build it so that the 14 Offices in Column B in sheet 1 are already entered and copied down the relevant number of times and then need to populate Column A with the relevant Client in blocls of 14. Does that make sense?

Many thanks in advance

R
 
Upvote 0
Richie

This is a different question. I think you should create a new thread describing the best you can what you need. Doing so others members can also help.

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Taking one value from a list on one sheet and duplicating it multiple times in a list in another
2
Replies
17
Views
1K
MickG
M
R
  • Question Question
A mix of Merged/Split Cells in a row to reflect a "family" of separate actions relatint to a "parent" cell?
Replies
1
Views
682
RichieA
R
P
  • Question Question
Problem with running 2 different macros
Replies
5
Views
929
pupsia
P

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top