Vlookup

Chaim T

Hello,

 

i have a sheet with billed claims including a column "Pending Amount", and "Detail ID", each line item has a different detail ID, now i want to update the pending amount column according to another sheet, i tried the simple Linking Cell method but it did not imported the pending amounts to the right detail ID's. and i anyway don't want to actually link the cells i just want to update once the amounts

 

Any one can help please?

 

Paul Newcome

I feel like an INDEX/MATCH may work for you, but would you be able to provide more detail and some screenshots just to be sure?

Chaim T

Thank you for replying, 

i did not find instructions how to use an index/match to return values base on data in another sheet

 

 

Paul Newcome

Are you able to provide more details/screenshots of what you are trying to do?

Chaim T

Please see screenshot bellow,

the pending amount column in the sheet below should be updated according to the pending amount column of the sheet above. each number to the correct row base on the Detail ID Column 

 

 

Paul Newcome

Your basic VLOOKUP would be as follows:

 

=VLOOKUP([Detail ID]@row, {Original Sheet Range}, 6, false)

 

{Original Sheet Range}: For this you would use the "Reference Another Sheet" link in the formula helper box. Select the columns from Detail ID all the way to Pending Amount.

 

While this will work, it has extremely low flexibility. To use a much more flexible INDEX/MATCH, it would look something like this...

 

=INDEX({Original Sheet Range 1}, MATCH([Detail ID]@row, {Original Sheet Range 2}, 0))

 

{Original Sheet Range 1}: Using the cross sheet referencing directions above, select ONLY the pending amount column.

 

{Original Sheet Range 2}: Same as above except you will select ONLY the Detail ID column.

Chaim T

Hi, thanks for replying, the index/match looks to work good,

 

what if i want to do the same thing but match all of specific columns like Patient and date of service and charge amount ? i understand that instead of [Detail ID] i has to add a "and formula" with the other column name

Chaim T

anybody can help me?

Paul Newcome

You would just have to change your initial range. It would look something like this...

 

=INDEX({Original Sheet Range 3}, MATCH([Detail ID]@row, {Original Sheet Range 2}, 0))

 

Range 3 would simply be the info you are wanting to pull for that particular column. In the next column over you would use

 

=INDEX({Original Sheet Range 4}, MATCH([Detail ID]@row, {Original Sheet Range 2}, 0))

 

being sure to designate the appropriate column on the original sheet as the range for the matching column in the formula sheet.

Chaim T

Thank you, Paul, works good

Paul Newcome

Excellent. Happy to help. Sorry for the delay. The end of the year is always excessively busy for me.