VLOOKUP with Condition

[anelem]

Hi
Sheet 1 :
Col A Col B Col C
Row1 Joe Green 45100
Row2 Joe Red 30000
Row3 Joe Blue 15000
Row4 Sag Green 2077
Row5 Sag Red 31001
Row6 Sag Blue 45000

Sheet 2 :
Col A Col B Col C
Row1 Joe Green ?
Row2 Joe Red ?
Row3 Sag Blue ?
Row4 Sag Green ?

Looking to get answer in Sheet 2 Col C
So Sheet 2 Col A must match to Sheet 1 Col A
When found, Lookup Sheet 2 B1 on Sheet 1 Col B
When found, return contents in Col C

Hope I have explained this well.
If anyone can please assist will make my job easier for me.
Thanking you in advance

 

[etaf]

not sure if column A has row - or if that was illustration

Name in column A on both sheets
Values in column B on sheet 1

and you want those values to be matched against the name on sheet 2

=Vlookup(Sheet2!A1, Sheet1$A$2:$B$100,2,false)

example on 1 sheet so you can see the principle

Book1
	A	B	C	D	E	F	G
1	Sheet 1				Sheet 2
2	Joe Green	45100			Joe Green	45100
3	Joe Red	30000			Joe Red	30000
4	Joe Blue	15000			Sag Green	2077
5	Sag Green	2077			Sag Blue	45000
6	Sag Red	31001
7	Sag Blue	45000
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=VLOOKUP(E2,$A$2:$B$7,2,FALSE)

 

[anelem]

Hi, thanks for the very quick reply . .
Sorry my layout was not very clear.

Sheet 1				Sheet 2
Joe	Green	45100​		Joe	Green	?
Joe	Red	30000​		Joe	Red	?
Joe	Blue	15000​		Sag	Green	?
Sag	Green	2077​		Sag	Blue	?
Sag	Red	31001​
Sag	Blue	45000​

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C
1
2	Joe	Green	45100
3	Joe	Red	30000
4	Joe	Blue	15000
5	Sag	Green	2077
6	Sag	Red	31001
7	Sag	Blue	45000
Sheet1

+Fluff New.xlsm
	A	B	C
1
2	Joe	Green	45100
3	Joe	Red	30000
4	Sag	Green	2077
5	Sag	Blue	45000
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=INDEX(Sheet1!$C$2:$C$7,MATCH(A2&"|"&B2,Sheet1!$A$2:$A$7&"|"&Sheet1!$B$2:$B$7,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[etaf]

i would use an index/match to do the lookup
you will need a Control+shift+enter to make an array, microsoft 365 does that automatically now

Book1
	A	B	C	D	E	F	G
2	Sheet 1				Sheet 2
3	Joe	Green	45100		Joe	Green	45100
4	Joe	Red	30000		Joe	Red	30000
5	Joe	Blue	15000		Sag	Green	2077
6	Sag	Green	2077		Sag	Blue	45000
7	Sag	Red	31001
8	Sag	Blue	45000
Sheet2

Cell Formulas
Range	Formula
G3:G6	G3	=INDEX($C$3:$C$8,MATCH(E3&F3,$A$3:$A$8&$B$3:$B$8,0))

 

[anelem]

Genius!! Thanks I have used Etaf solution - very simple and so easy to understand.
Thank you I really appreciate your assistance.

 

[Fluff]

This may not be a problem with your data, but you could get wrong answers with etaf's solution

+Fluff New.xlsm
	A	B	C
1
2	JoeG	Reen	200
3	Joe	Green	45100
4	Joe	Red	30000
5	Joe	Blue	15000
6	Sag	Green	2077
7	Sag	Red	31001
8	Sag	Blue	45000
Sheet1

+Fluff New.xlsm
	A	B	C	D
1			Fluff	etaf
2	Joe	Green	45100	200
3	Joe	Red	30000	30000
4	Sag	Green	2077	2077
5	Sag	Blue	45000	45000
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=INDEX(Sheet1!$C$2:$C$8,MATCH(A2&"|"&B2,Sheet1!$A$2:$A$8&"|"&Sheet1!$B$2:$B$8,0))
D2:D5	D2	=INDEX(Sheet1!$C$2:$C$8,MATCH(A2&B2,Sheet1!$A$2:$A$8&Sheet1!$B$2:$B$8,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

Given that the results are numeric, why not simply use sumifs?

=SUMIFS(Sheet1!C$2:C$8,Sheet1!A$2:A$8,A2,Sheet1!B$2:B$8,B2)

 

[etaf]

@Fluff
Thats interesting, never thought of that issue, before, learning all the time, thank you, for pointing it out
I'm assuming the | is just a random character to separate the 2 cells , so your example of JoeG | Reen cannot happen
No significance in using the | just cleaner - BUT Z or 2 or any character would work

 

[Fluff]

No real significance to the |, just that it's unlikely to be in the data.
For instance if I used G as the delimiter & there was Joe Reen in A2,B2 you would still get the wrong answer.