Hi All,
I do have a question is i have to use vlookup to get data from another sheet as my main condition is supplier name and currency must same with another sheet of data to get the amount chargeable to my company. Thus is had tried used =VLOOKUP(A2&$B$1,Sheet2!A:C,3,0) as A2 is my supplier name,B1 is currency where i had locked this column formula for being changes as i copy all the formula to below. In sheet2 A=Supplier Name ,B=Currency and C=Amount Chargeable.May i know is there any solution as I get an error #N/A by using the formula.
Vlookup two Condition
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup two Condition
VLOOKUP can only look up information in the first column of a range.
One option is to use SUMIFS:
=SUMIFS(Sheet2!$C$1:$C$100, Sheet2!$A$1:$A$100, A2, Sheet2!$B$2:$B$100, $B$1)
Another is to use a combination of INDEX and MATCH in an array formula, confirmed with Ctrl+Shift+Enter:
=INDEX(Sheet2!$C$1:$C$100, MATCH(A2&$B$1, Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100, 0))
In both formulas, it is better not to refer to entire columns; I used rows 1 to 100 but you can change that to match your data.
One option is to use SUMIFS:
=SUMIFS(Sheet2!$C$1:$C$100, Sheet2!$A$1:$A$100, A2, Sheet2!$B$2:$B$100, $B$1)
Another is to use a combination of INDEX and MATCH in an array formula, confirmed with Ctrl+Shift+Enter:
=INDEX(Sheet2!$C$1:$C$100, MATCH(A2&$B$1, Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100, 0))
In both formulas, it is better not to refer to entire columns; I used rows 1 to 100 but you can change that to match your data.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Vlookup two Condition
Hi Mr Hans,
Thanks for your guidance, the formula had solved my problem.Really nice to meet with you.
Thanks for your guidance, the formula had solved my problem.Really nice to meet with you.
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Vlookup two Condition
Hi ,
I have using this formula =SUMIFS(Sheet2!$C$1:$C$100, Sheet2!$A$1:$A$100, A2, Sheet2!$B$2:$B$100, $B$1) but it is possible to replace the negative figure to positive using as all value in column C is in negative but in my report in sheet1 i need in positive.
I have using this formula =SUMIFS(Sheet2!$C$1:$C$100, Sheet2!$A$1:$A$100, A2, Sheet2!$B$2:$B$100, $B$1) but it is possible to replace the negative figure to positive using as all value in column C is in negative but in my report in sheet1 i need in positive.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup two Condition
Does
=-SUMIFS(Sheet2!$C$1:$C$100, Sheet2!$A$1:$A$100, A2, Sheet2!$B$2:$B$100, $B$1)
do what you want?
=-SUMIFS(Sheet2!$C$1:$C$100, Sheet2!$A$1:$A$100, A2, Sheet2!$B$2:$B$100, $B$1)
do what you want?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Vlookup two Condition
Hi Mr Hans,
That's right.Thanks a lot.
That's right.Thanks a lot.