IF and AND formula

[RAJESH1960]

Hello formula experts
I have these 3 sheets in the workbook and I want to get the data in sheet C from sheet B depending on the sheet A. In short, it is 2 if conditions in one formula. If the cell match 2 conditions then get Taxable value in cell H2 else show blank.
1. =IF(LEFT(B!A7,2,=A!$A$1
2. =IF(B!I7='C'!$H$1
Can someone please tell me the correct formula to apply in cell C2 and get the right answer.?
something is wrong with XL2BB. When I select the Capture range > select all, the buffering doesn't stop and I have to close the sheet.
Get Amount in GST class.xlsm

 

[RAJESH1960]

I tried this formula but I am still not getting an error that you have entered too many arguments for this function.
=IF(AND(LEFT(B!A7,2=A!$A$1,'C'!$H$1=B!I7,B!J7,"").
Wonder where are all the experts and why are they not responding.

 

[etaf]

i suspect there maybe more required

=IF(AND(LEFT(B!A7,2)*1=A!$A$1,B!I7='C'!$H$1),B!J7,"")

Note - when you use a text function like left - it returns text, and then you are comparing to a number 29
so i multiply the text value 29 by 1

 

[RAJESH1960]

i suspect there maybe more required

=IF(AND(LEFT(B!A7,2)*1=A!$A$1,B!I7='C'!$H$1),B!J7,"")

Note - when you use a text function like left - it returns text, and then you are comparing to a number 29
so i multiply the text value 29 by 1

That worked perfectly. Thanks etaf. Thanks for the explanation too. Noted.
Even my opening and closing brackets were placed wrong.

 

[etaf]

you are welcome

 

[RAJESH1960]

etaf are you still there ?

 

[RAJESH1960]

Can you help me freeze the cells correctly. I have to drag the formula to 12 rows till I get the amount in one of them. ? Columns H to M will display the number if the left condition is fulfilled. The formula in column N to S has to be if the left condition is not fulfilled. I freezed the wrong cells by mistake.
It is =IF(AND(LEFT(B!$A$7,2)*1=A!$A$1,B!$I$7='C'!H1),B!$J$7,""). Still not sure. Working on that.
Just give numbers from H to S as shown in the image. Then drag the formula till N. It will show the result in one of the cells if both conditions are fulfilled.
In column N the formula changes to =IF(AND(LEFT(B!$A$7,2)*1<>A!$A$1,B!O7='C'!N1),B!P7,"") which has to be dragged till S. Out of the 12 columns only one has to show the result.

 

[RAJESH1960]

I have to apply 2 different formulas one in cell H2 and drag it till cell M2. Another formula in cell N2 and drag it till S2.
Both the formulas I edited are working in one row only. I am able to drag the formula to the right and I am getting the right answer. But when I drag the formula down I am not able to get the right answer as the cells are freezed in such a way.
Formula for cell H2 is

=IF(AND(LEFT(B!A$7,2)*1=A!$A$1,B!$I$7='C'!$H$1),B!$J$7,"")

and the formula for cell N2 is

=IF(AND(LEFT(B!$A$7,2)*1<>A!$A$1,B!$I$7='C'!N1),B!$J$7,"")

.
Please help me to edit in such a way that when I drag down the formula down I should get the correct result and in such a way that it should not change the answer in the top row horizontally.

 

[RAJESH1960]

Tried every possible way, but looks like unless I enter manually for each row in one cell then only then it will work.
Unless you come up with a solution.

 

[etaf]

the $ fixes the refereence
$A fixes the column
$1 fixes the row

so if you want the column to be allowed to change as you drag horizontally

then - =IF(AND(LEFT(B!A7,2)*1=A!$A$1,B!I7='C'!H$1),B!J7,"")
Now as you copy along it will look for h1 then I1 then J1

BUT i'm not sure what you are after exactly
I suspect this is a lot more complicated

can you explain some more with examples -
I'm in UK so will be signing off soon and not around so much for next couple of days - sorry

Are you trying to lookup values in sheet B column J based on what matches column B I based on the row 1 in sheet C !!!!
what about the 2nd criteria 29 in sheet A
any where close or missing it completely