IF/THEN copy value

mgilkessmith

I have two sheets and three columns. I would like to write a formula in Column A of Sheet 1 that will read Column B of Sheet 2. If the cell in Column B matches "X" then return the value in the cell of Column C on Sheet 2.

Is this possible with VLOOKUP or INDEX/MATCH?

Thank you in advance!

Melissa

Paul Newcome

This sounds like a standard INDEX/MATCH to me.

=INDEX({Column C}, MATCH([Column A]@row, {Column B}, 0))

mgilkessmith

Thank you, Paul. I am getting an #UNPARSABLE.

Maybe a better explanation is:

I want Column A to look at Column B for a specific number, let's say 10. If the number in the cell is 10 then I want to return the value in the same row in Column C.

Paul Newcome

Right. You'll need to create the cross sheet references as need and make sure you use the column name in your sheet in place of [Sheet A].

If that doesn't fix it, please copy/paste the formula that is giving you the error from the sheet to here.

mgilkessmith

@Paul Newcome

Do I want to use an INDEX/COLLECT?

In Sheet B cell I would enter the formula for INDEX {Sheet A - Range 1} looking for "10", IF "10" is found then return the number from {Sheet A - Range 2}@row

Paul Newcome

An INDEX/MATCH should be all you need since you are only matching in a single range. You only need the collect if you needed to narrow it down by more than one range/criteria set.

mgilkessmith

@Paul Newcome

It would be an INDEX/MATCH even if there is no MATCH? The same number is not on both sheets. I'm looking for a specified "value" in Sheet 2 - Column B. If that value is there, I would like it to return the value from Sheet 2 - Column C to Sheet 1 - Column A.

=IFERROR(INDEX({Column B}, "value", COLLECT({Column C}@row), 0, "")))

Paul Newcome

If there is no match, then the COLLECT function wouldn't work either. Are you able to provide screenshots?

mgilkessmith

@Paul Newcome Thank you for all of your help. I think I figured out a better way to do this.