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 cross-sheet 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 name-column 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 cross-sheet 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 name-column 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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!