Vlookup two Condition

[JERRY89]

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.

[HansV]

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.

[JERRY89]

Hi Mr Hans,

Thanks for your guidance, the formula had solved my problem.Really nice to meet with you.

[JERRY89]

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.

[HansV]

Does

=-SUMIFS(Sheet2!$C$1:$C$100, Sheet2!$A$1:$A$100, A2, Sheet2!$B$2:$B$100, $B$1)

do what you want?

[JERRY89]

Hi Mr Hans,

That's right.Thanks a lot.