Return a value of Column A if a value in column B of different sheet is the same
hi;
hope my english is comprehensible
I have two sheet with some same column and quite a few different column. Say sheet X and sheet Y
I want to use a formula for;
return the value of column A in sheet Y
if a value in similiar column in sheet X is the same with sheet Y.
column A in sheet Y might be blank. which means the value returned should also be blank
i tried a combination of IF and COLLECT. IF and MATCH. IF and FIND. All either #INVALID DATA TYPE or #UNPARSEABLE
my last formula
=VLOOKUP("=[Kode Kapling]@row"; {Daftar Sales Order in Progress Range 5}; 3)
so basically. if {Daftar Sales Order in Progress Range 5} is = [Kode Kapling]@row) > both is the same column type and information
than i want the cell to be filled with a value in sheet {Daftar Sales Order in Progress} in column number 3 of the same row
the response is #incorrect argument set
this is sheet X where i want the formula is
and this is the 'source sheet'
thx before
Best Answers

Try this. I think I am pulling in the right column you referenced I believe you are trying to bring the [No SPU] into your target sheet.
=INDEX({Source Sheet No SPU}, MATCH([Kode Kapling]@row, {Source Sheet Kode Kapling},0))
You will need to create these crosssheet references you cannot just copy paste this formula into a cell. Each {range} is a single column. You can name them anything you like.
Does this work for you?

Hi Andri
Looking at your data, it appears you are pulling in the [Link] column from your source sheet (you may consider always changing the name of the {range} to match the sheet namecolumn name so it is clear in formulas what the {range} actually refers to). From the screenshot you provided above, the Link column in the source sheet has many blank cells. The Index Match will pull in whatever is in that cell  if the cell is blank then that is what is pulled through. Am I interpreting the formula correctly? I need more information please
I see that some cells were not matched. If you wanted #NO MATCH cells to remain blank, or show a different message, you can do that using the IFERROR function.
=IFERROR(INDEX({Daftar Sales Order in Progress Range 1}; MATCH([Kode Kapling]@row; {Daftar Sales Order in Progress Range 5}; 0));"")
This says run the Index Match formula but if there is an error, keep the cell blank
Answers

Forget to type my last formula
=VLOOKUP("=[Kode Kapling]@row"; {Daftar Sales Order in Progress Range 5}; 3)
so basically. if {Daftar Sales Order in Progress Range 5} is = [Kode Kapling]@row) > both is the same column type and information
than i want the cell to be filled with a value in sheet {Daftar Sales Order in Progress} in column number 3 of the same row
the response is #incorrect argument set

Try this. I think I am pulling in the right column you referenced I believe you are trying to bring the [No SPU] into your target sheet.
=INDEX({Source Sheet No SPU}, MATCH([Kode Kapling]@row, {Source Sheet Kode Kapling},0))
You will need to create these crosssheet references you cannot just copy paste this formula into a cell. Each {range} is a single column. You can name them anything you like.
Does this work for you?

yes,
=INDEX({Daftar Sales Order in Progress Range 1}; MATCH([Kode Kapling]@row; {Daftar Sales Order in Progress Range 5}; 0))
it works!
i got a lot of insight of how INDEX and MATCH works, thx a lot @KDM !

hi @KDM
I just found something odd
i converted the formula to column formula. but some rows is blank.
i cant find out why

Hi Andri
Looking at your data, it appears you are pulling in the [Link] column from your source sheet (you may consider always changing the name of the {range} to match the sheet namecolumn name so it is clear in formulas what the {range} actually refers to). From the screenshot you provided above, the Link column in the source sheet has many blank cells. The Index Match will pull in whatever is in that cell  if the cell is blank then that is what is pulled through. Am I interpreting the formula correctly? I need more information please
I see that some cells were not matched. If you wanted #NO MATCH cells to remain blank, or show a different message, you can do that using the IFERROR function.
=IFERROR(INDEX({Daftar Sales Order in Progress Range 1}; MATCH([Kode Kapling]@row; {Daftar Sales Order in Progress Range 5}; 0));"")
This says run the Index Match formula but if there is an error, keep the cell blank
Help Article Resources
Categories
Check out the Formula Handbook template!