Logic for dynamically determining cell value

[BrianKusch]

Hello - I need to be able to automatically populate one column's (Q) values based on a combination of multiple other column values. Here is the scenario:

I have a report pulled from the accounting software. Column E is Inv#, Column F is Company Name & Column K is Shipping cost. There are two parts to this report (one sheet though). The top half, has all sales for the month while the bottom half only contains those from the top that included shipping costs. I need to be able to match the records from the bottom to the records at the top and automatically populate Column Q with the appropriate shipping cost for that invoice line item.

Example:

Top Report

E F Q --> this column should be able to grab the shipping value below (Row K) when applicable
122 Company 0 $0
123 Company 1 $3
124 Company 2 $12.67
125 Company 3 $7.22
126 Company 4 $0

Bottom Report

E F K
123 Company 1 $3
124 Company 2 $12.67
125 Company 3 $7.22


Im not sure which functions to use or if this can even be done... please help!

[DGagnon]

you can use a vlookup to do this:

where E1 = item number, and $E$10:$K$20 is the range of your bottom report

=VLOOKUP(E1,$E$10:$K$20,7,0)

[BrianKusch]

Hi - Thanks for your reply. When I tried the solution you recommended, i am receiving an error (#N/A).

[DGagnon]

you will get the #N/A error when it is unable to find a match, can you attach a sample so i can try to figure out where the issue is?

[BrianKusch]

DGagnon - I got it... WITH YOUR HELP! Thanks!!

=IF(ISNA(VLOOKUP(E3,$E$157:$K$205,6,0)),0,(VLOOKUP(E3,$E$157:$K$205,6,0)))

Rep+