Match/Lookup Help

[penbeacho]

Hello,

I am having trouble with creating the working logic for a spreadsheet. I have a list of ID that have corresponding values. What Im seeking to do is take the Employee ID and match it to a master list, once that location is found I want to take the adjacent value from the lookup list and substitute the value in the master list. I tried Match and lookups, with no success.

The logic would be as follows:
'Column B Employee Number Change
'Column D New Contribution Change Value
'Column G Employee Number Master List
'COlumn H Contribution List

'If ColB = ColG then Replace ColH with ColD


I have a spreadsheet with several columns of data, I have simplified it to prove the desired concept. Eventually I would like to develop VBA code for the spreadsheet to perform the task by clicking a button.
screen.gif

Hope SOmeone can provide some insight in solving this issue.

[FDibbins]

Hi, welcome to the forum

1st, Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

2nd, if you are trying to return a value based on criteria, you probably need to use vlookup()...

	A	B	C	D
1	aa	1	bb	2
2	bb	2
3	cc	3
4	dd	4
5	ee	5

C1=search criteria
D1=VLOOKUP(C1,$A$1:$B$5,2,0)

[penbeacho]

sorry about that I have attached a file.

[simarui]

what issue did you have with match / lookups..? seems to me this should work:

Please Login or Register  to view this content.

(untested)
or

Please Login or Register  to view this content.

it's also pretty easy in vba - here's one way:

Please Login or Register  to view this content.

[soberguy]

I don't understand what you want to go where.

[penbeacho]

Column G and H are Master list, i am trying to take the list from column B and match the values in column G once that is found i want to substitute the adjacent value in column D into the matching adjacent location in column H.

[simarui]

i must be lagging or something, suddenly saw several replies...

it looks like perhaps you want to keep a running total? where you input a new contribution amount in column G and then hit update and that amount gets added to whatever was already in column D, is that correct?

I'd suggest it's wise to keep the full record of all contributions and then you can just do a sumif() to get the cumulative total, but if you are set on replacing the amounts in column G with new amounts then you can modify my earlier code very slightly and i think accomplish what you're trying to do:

Please Login or Register  to view this content.

[penbeacho]

The closest thing I have to finding the location is the Match, but this tells me the value location relative to the search list value and not the actual location on the list, When trying to do this with a vlooukup I had #NAME Error show up.

[soberguy]

in D2:
=INDEX(G$2:H$17,MATCH(B2,G$2:G$17,0),2)

Copy and paste down.

[penbeacho]

I was able to find a similar logic using vlookup, the issue here was that this method finds the actual value on the h column while what I want to do is substitute the value from column d in its place. Column B is IDs for people that had a change, Column D are the changes. Column H should have the matching ID values substituted (fromD to H).

[FDibbins]

you want to replace an existing cell contents with something that a formula finds for you?

[penbeacho]

I want to find the location of the id number in the master list, then I want to replace the adjacent cell value (column H) with the new value from column D. If you look at the larger file there is a sheet with how the master list should look when done.

[simarui]

it sounds like we all had your desired output reversed - all of our formulas find the ID in G and pull H into D... fixing that is as easy as switching the references around:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

or in vba

Please Login or Register  to view this content.

should all work

[penbeacho]

the formula worked beautifully!!! I will test the vba shortly. Thankyou!!