KB58 |

KB52 |

KB69 |

KB05 |

KB68 |

KB04 |

JY025 |

<colgroup><col></colgroup><tbody>

</tbody>

KB6 | 130 |

KB5 | 130 |

KB2 | 125 |

KB1 | 120 |

KB0 | 100 |

JY025 | 180 |

JY015 | 140 |

<colgroup><col><col></colgroup><tbody>

</tbody>

basically I want to be able to add a cell/column in the first data set which will lookup the cell ie KB58 against the lookup range and give the amount if it contains the lookup value in the range. So in this case KB58 should be 130.

I would think this would be really easy to do but for some reason Excel tries to be difficult. I can do a vlookup for exact match so that JY025 and JY015 will be 180 and 140 respectively but the rest seem to give incorrect results (obviously).

I did a google to figure out what to do but didn't find a workable solution without involving convoluted formulas (which were not workable anyway for me) or vba (which I don't want) .

The excel file is an invoice sheet and the amounts in the lookup range would vary depending on customer or shipping etc. so lookup range has to be in a separate sheet

Hope I have been clear, please let me know what I need to give in order to make this easier to answer. Using Excel 2016 but the solution should work with anything over 2007.