Home / Questions / 360570
Accepted
Franco Torres
Asked: 2020-05-30 11:35:17 +0800 CST 2020-05-30 11:35:17 +0800 CST

How to compare cell against set of cells and get an associated value

  • 772

I need to compare the date of a cell against a column that has several dates, see which one matches and get the associated value. Here is an image to explain better:

enter image description here

Column D is where I need to put the formula, which compares the dates in column A of sheet 1 with the dates in column A of sheet 2. If it matches, I need to multiply the values ​​in column C of sheet 1 by the values ​​in column B of sheet 2.

Here is another image with an example to be clearer

enter image description here

I have a little idea that would be something like: =IF(ISERROR(MATCH(Sheet1!A4;Sheet2!A2:A32;0));Sheet1!C4*....

Where the ellipses are, B3 should go, but I don't know how to get that cell in the search above. I'm waiting for some ideas, thank you very much. :)

excel

1 Answers

  1. Best Answer

    It is not as complicated as it seems, you only have to use the function BUSCARVin which you must place the date in your comparison parameter, the range Hoja 2where said date must be searched, the column indicator, that is, how many columns must be moved to find the value you want to return and then multiply it by the corresponding value in column C, something like this:

    =SI.ERROR(BUSCARV(A2;Hoja2!$A$2:$B$3;2;0);0)*C2

    This is the result:

    Result

    I put the SI.ERRORin case the date did not exist or that the associated cell was empty, it returns 0 and in the range Hoja2!$A$2:$B$3since I did a test with few data, you must put your complete range of the Hoja 2. I hope I've helped. Greetings.

    • 2