doing some lookup/index in a checksum function

[dzorug]

hi, i have a worksheet called 'data', where there are several columns, the only relevant ones being:
-- A: names
-- E, F and G: numbers

E, F and G are connected such that E must equal F+G (it's actually more complicated than that, but i'm trying to simplify it here)

I need to create a worksheet, called 'test', to ensure that, for whichever A i'm checking, E=F+G.

In 'test', I put a name in A1. I'd like to create an IF function in C1 to search data!A1 to data!A50 for the name that matches test!A1, and return 'Correct' or 'Wrong' depending on whether the respective E=F+G.

For example, if test!A1="bob" and data!A28="bob", I'd like test!C1 to return "Wrong" if data!E28 is not equal to data!F28+data!G28.

Problem is, I don't know how to put that all into a function. Any help?

- Kenneth

[frankybenali]

Try this:

=IF((VLOOKUP(A1,Data!A1:D4,2,FALSE)+VLOOKUP(A1,Data!A1:D4,3,FALSE))=VLOOKUP(A1,Data!A1:D4,4,FALSE),"Correct","Wrong")

[olasa]

Try this:
=IF(SUMPRODUCT((A:A="Name")*(E:E=F:F+G:G)),"Correct","Wrong")

Ola Sandstrom


Note: I just examplified the principle. Fill in your sheetnames and ranges.

[dzorug]

thanks franky! works great!