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

 

[etaf]

if you put
=IF(AND(LEFT(B!$A7,2)*1=A!$A$1,B!$I7=H$1),B!$J7,"")
into H2 = then copy across and down
does that work

 

[RAJESH1960]

if you put
=IF(AND(LEFT(B!$A7,2)*1=A!$A$1,B!$I7=H$1),B!$J7,"")
into H2 = then copy across and down
does that work

Yes it is showing one value which is right.

 

[RAJESH1960]

The H to M columns are working. Now need to edit the code in columns N to S.
I was able to drag the formula from H to M to down.

 

[etaf]

ok, so i have fixed the columns and rows with $ - so as its dragged those dont change
=IF(AND(LEFT(B!$A7,2)*1=A!$A$1,B!$I7=H$1),B!$J7,"")
as it dragged across - then the columns are the ones that will change
so the only column thats allowed to change is =H$1 now that will change to I,J,K etc as dragged to the right
and when dragged down - its the rows
so the rows that will change are
(B!$A7,2
B!$I7
,B!$J7
as you go down they will change to 8,9,10,11 etc

see if you can now based on that can change N to S

 

[RAJESH1960]

I got the other formula to correct.
=IF(AND(LEFT(B!$A7,2)*1<>A!$A$1,B!$I7=N$1),B!$J7,"")

 

[etaf]

cool

 

[RAJESH1960]

I just did that. Based on your formula I had to replace = with <>

 

[RAJESH1960]

Thanks Man. . You really solved the biggest problem in this project.

 

[etaf]

ok
you are welcome

the $ is an important part of excel
known as relative and absolute addressing
so good to understand how that works

probably used in every excel sheet setup at some point