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

[RichieA]

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! 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.

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

Sheet 1
Client	Office	Value	Next Action	Latest Event
Alpha Inc	Austria		Arrange meet	Sold 1000 units
Alpha Inc	Belgium		Supply goods	Visit complete
Alpha Inc	Canada		Supply goods	Visit complete
Alpha Inc	Denmark		Shut down	Shut down
Alpha Inc	Egypt
Bravo Inc	Austria
Bravo Inc	Belgium
Bravo Inc	Canada		Rugby	Lunch
Bravo Inc	Denmark
Bravo Inc	Egypt
Sheet 2
	Austria	Belgium	Canada	Denmark	Egypt
Alpha Inc	25	63	564	34	67
Bravo Inc	42	40	32	56	40
Charlie Inc	23	29	45	61	21
Delta Inc	33	568	98	52	231
Echo Inc	97	238	56	21	47

<tbody>
</tbody>

 

[Marcelo Branco]

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.

 

[RichieA]

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

 

[Marcelo Branco]

To understand the formula take a look at
http://www.contextures.com/xlFunctions03.html

Good luck!

M.

 

[RichieA]

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

 

[Marcelo Branco]

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.

 

[RichieA]

Ah yes OK, will do. Many thanks again.

R

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.