I need urgent help in Excel please

[Aristotélēsticus]

I’m working on this excel file for professional purposes where I need to create a function that runs through two different columns in a Table A, and compare it with cells in a column in Table B, then returns data from another column in Table B.

 

If you’re starting to feel somehow dizzy, I will try to put it in a simple way.

 

What do we already have?

 

1. Table A that has a buyer_id column, and a seller_id column.

2. Table B that has a client_id column and a client_name column.

3. Table C, where the function should be inserted to return the client’s name.

 

What is needed to be done?

 

Insert a function in Table C that does the following:

1. Check the buyer_id and seller_id in table A.

2. Check client_id in Table B.

3. Find matches between client_id and the other two columns seller_id, buyer_id, if there are ones.

4. If a match is found, then return the corresponding client_name from Table B.

5. Else, return FALSE.

 

I have came up with a certain formula of “ifs” and “ors”, but the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

 

Is there anyone in here who can lend me a hand in this please?

 

Thanks in advance.

[Astrotoy7]

what an unusual post!

 

Excel is not my strong suit at all... if anyone around here has no answers, head over here:

 

http://www.excelforum.com/excel-general/

 

good luck!

 

mtfbwya

[jonathan7]

I’m working on this excel file for professional purposes where I need to create a function that runs through two different columns in a Table A, and compare it with cells in a column in Table B, then returns data from another column in Table B.

 

If you’re starting to feel somehow dizzy, I will try to put it in a simple way.

 

What do we already have?

 

1. Table A that has a buyer_id column, and a seller_id column.

2. Table B that has a client_id column and a client_name column.

3. Table C, where the function should be inserted to return the client’s name.

 

What is needed to be done?

 

Insert a function in Table C that does the following:

1. Check the buyer_id and seller_id in table A.

2. Check client_id in Table B.

3. Find matches between client_id and the other two columns seller_id, buyer_id, if there are ones.

4. If a match is found, then return the corresponding client_name from Table B.

5. Else, return FALSE.

 

I have came up with a certain formula of “ifs” and “ors”, but the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

 

Is there anyone in here who can lend me a hand in this please?

 

Thanks in advance.

 

Easiest way to do it would be to write a macro and then run it, especially if your going to be running this every day/wee/month to avoid having to keep doing forumla's - There are a lot of guides to using macro's here's one - http://office.microsoft.com/en-us/excel-help/introduction-to-custom-macros-in-excel-HA001118958.aspx

[Samuel Dravis]

You probably want to use http://vLookUp somewhere in there. I can't write something for you right now since I'm going to work, but I'll look at it later.

[Aristotélēsticus]

Thank you guys for your help.

 

Still didn't get what I want from the sources you gave me.

 

But thanks for trying. I appreciate it.

[Samuel Dravis]

I messed around with it a bit. This should be more or less what you need.

 

edit: accidentally didn't make the formula quite right: it was supposed to be

=IF(OR(IF(ISERROR(VLOOKUP(client_id,buyer_id,1,FALSE)=client_id), FALSE, TRUE), IF(ISERROR(VLOOKUP(client_id,seller_id,1,FALSE)=client_id), FALSE, TRUE)),client_name, FALSE)

 

I fixed the download also.

[Aristotélēsticus]

I messed around with it a bit. This should be more or less what you need.

 

edit: accidentally didn't make the formula quite right: it was supposed to be

=IF(OR(IF(ISERROR(VLOOKUP(client_id,buyer_id,1,FALSE)=client_id), FALSE, TRUE), IF(ISERROR(VLOOKUP(client_id,seller_id,1,FALSE)=client_id), FALSE, TRUE)),client_name, FALSE)

 

I fixed the download also.

 

I have revised your formula to fit my worksheet but I did not get what I want. I've already tried a similar formula but the problem is still as stated above.

 

the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

 

And I cannot download the file.

 

Thanks for your help mate.

[Samuel Dravis]

I have revised your formula to fit my worksheet but I did not get what I want. I've already tried a similar formula but the problem is still as stated above.

 

the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

 

And I cannot download the file.

 

Thanks for your help mate.

I'm not sure I understand the problem. My formula gets the client_id and checks it against the entire columns seller_id and buyer_id, one after the other, not just one row ("buyer_id" in the formula is the name of column A1 on Sheet1, and similarly with the others). If it finds a match in either column, the OR returns TRUE and the top level IF will put the client_name on the same row as the client_id. You can test this behavior in the example sheet. What exactly is it doing wrong?

 

As for the download, try this. Not sure why the GoogleDocs one isn't working for you.

[Astrotoy7]

As for the download, try this. Not sure why the GoogleDocs one isn't working for you.

 

Just out of curiousity > I tried downloading this too - results:

 

*when signed into my google account: works fine

 

*when not signed into my google account - you get this

>> the download link wont work either, tested on FF and IE8

 

hence, to get around the error, simply sign into your google/gmail account, if you have one

 

mtfbwya

[Aristotélēsticus]

Astrotoy7's conclusion was correct. It is my fault after all so apologies.

 

Thank you for your efforts Samuel Dravis, and thank you all guys for your help, but judging from the dynamic nature of buyer_id, seller_id data, and the new requirements, guess a macro will become a must sooner or later.