Change Cell Value Based on INDEX/MATCH Criteria

[freybe06]

Hello Everyone,

I'm running into a little issue and I'm hoping someone can help me. I'm looking to change the value of a cell based on a VLOOKUP. Using the attached spreadsheet information as an example, I'd like to write code that says "In Sheet2, Column B, if you INDEX/MATCH the adjacent value in column A to Sheet1, and the value comes out as either Wednesday or Friday, then multiply the value by 1000." In the spreadsheet, you will see that "James" and "Joe" are next to "Wednesday" and "Friday" respectively on Sheet1. Therefore, their values on Sheet2 Column B are multiplied by 1000 and the rest of the values are left alone.

ChangeValueUsingVLOOKUP.xlsm

If someone could help me figure out the best way to code this, I'd really appreciate it!

Thanks in advance!

[JieJenn]

List your original values in B1, and in C1, enter =IF(OR(INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$B$1:$B$7,0))="Wednesday",INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$B$1:$B$7,0))="Friday"), B1*1000,B1) then fill the formula

[oeldere]

See the green cells in the attached file.

I made this with a vlookup after a index/match.

The formula is in the sheet.

[FDibbins]

Perhaps this, copied down?

=IF(SUM(COUNTIFS(Sheet1!$A$1:$A$7,{"Wednesday","Friday"},Sheet1!$B$1:$B$7,Sheet2!A1))=1,B1*1000,B1)

[freybe06]

Hi JieJenn,

Thank you for your attempt. However, I was hoping to do this without the use of another column. That's where the complexity comes in. Otherwise, just calculating the value in a different column would be a cinch.

Thanks

[oeldere]

What is the trouble of a helping column (such as JieJenn used)?

You can hide a column if you want.

[FDibbins]

Sometimes, using a helper column makes a lot of sense...it helps keep things relatively simple, and can help avoid overly-complex/ARRAY formulas - it can also help with understanding the flow/sequence of your process. The helper can always be hidden if needed.

I did not uise a helper, but I could have

[freybe06]

Hey Everyone - I agree that helper columns can definitely make things easier when trying to understand the process. As for this project, I was looking more for something that I can code in VBA that would update the necessary cells, not a formula within the cell (or helper column).

I can always use a helper column to build the formula then copy and paste the values into Column B and delete the helper column, but again, I was hoping to be able to find code that could just update the necessary values.

Thanks again for all your help! I really appreciate it!