Return a value of Column A if a value in column B of different sheet is the same

andri witana

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

Find more posts tagged with

Accepted answers

Kelly Moore

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?

Kelly Moore

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

All comments

andri witana

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

Kelly Moore

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?

andri witana

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 !

andri witana

hi @KDM

I just found something odd

i converted the formula to column formula. but some rows is blank.

i cant find out why

Kelly Moore

Hi Andri

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.