Comparing two columns, and returning values in the 4th column from the 3rd column

**shtuamouth** · 03-12-2014, 07:47 PM

hi.
abc.xlsx
yyyyyyyyyyyyyy.jpg zzzzzzzzzzzz.jpg

i have 1 worksheet say Accounts. There are columns A and B which correspond to each other. Then i have another worksheet say called Checks.

Its column B is the same as that of column A of Accounts worksheet but with values spread out in no order.

I want column C to be filled with the values of column B of Accounts worksheet but the values must correspond to based upon what is there in Column B of the Accounts worksheet.

i have attached the excel file may you want to test it.

Kindly help me in this.

**Richard Buttrey** · 03-12-2014, 08:09 PM

Hi,

I can see no matches between column B on CheckExpenseItems and column A on Accounts. However if there were this formula would return the column B of accounts

Formula:

=INDEX(Accounts!B:B,MATCH(B2,Accounts!A:A,FALSE),1)

**shtuamouth** · 03-13-2014, 01:53 AM

hi richard.

i tried the formula but it does not seem to work. can you have a look at the excel file uploaded above and kindly check why it is not working. i would be grateful my friend.

**shtuamouth** · 03-13-2014, 02:02 AM

the file uploaded above contains many data than the one i showed above in the pics. my concern is the checks sheet. 2 columns of concern A and F in the accounts sheet.

Checks sheet column AA is the same as A above with values unevenly dispersed. Yes the are the same if you scroll down you may find very few data similar. actually i exported just 100 transactions to excel from quickbooks so i think thats y this issue.

anyhow i want to fill in column Y of Checks with values of column F of Accounts but with respect to column AA of Checks which will be matched (i think) to Column A of Accounts.

**Richard Buttrey** · 03-13-2014, 05:50 AM

Hi,

I'm sorry but this is no clearer. Your original mentioned only columns A, B & C. Now you've thrown in F Y & AA.

You need to clearly state which columns you want to compare, and then when a match is found between a value in a Sheet1 column and the same value in a Sheet2 column, which column from Sheet1 you want to pick up.

But importantly you need to manually add the results you expect to see and tell us which is the column containing the results.

**shtuamouth** · 03-13-2014, 04:20 PM

thanx newdoverman.

this is exactly what i want.

can you provide me the formula without clicking the cells or columns. eg. instead of showing table1ID, rather show like $A$2:$A$10.

you can provide me the formula for your own worksheet.

i tried it myself but its not working that way so thats y i ask.

one more thing if drag the last cell W10 further down will it automatically fill in the 4 digit codes (given that there is data entered in columns U and V beyond row10)

**shtuamouth** · 03-13-2014, 02:11 PM

Hi Richard.

I am sorry about that and i will try to make it simple this time. i am starting afresh here so consider the responses above as void. i have re-attached 2 snaps as under. Kindly see them. Each is from a worksheet within a workbook. there are 2 worksheets named Accounts and Checks.

Accounts worksheet.jpg Checks Worksheet.jpg

Accounts worksheet has 3 columns. Column B contain account names, column C contains the corresponding account codes and column A contains the corresponding ID.

My concerned worksheet is Checks.
in that, Column U contains some of the account names and the corresponding ID under column V (similar to columns A and B). i created an empty column W and want the account codes of Column C (Accounts worksheet) to auto-fill here based on the values in column V.

I am new to excel so i don't know what to match with what or which values to lookup.
simply put i want column W to show account codes as in Column C.

Note that these worksheets are not limited to just 10 rows. so i need a formula that would do the job and which would apply to the whole Column W so that if any new entries are made i.e. beyond the 10 row, Column W would automatically display the account codes based upon what is there in either column U and/or V.

**Richard Buttrey** · 03-13-2014, 02:33 PM

Hi,

Snapshots are little use to us (see guidance in the rules). Please upload the workbook and don't forget to manually add examples of the results, clearly explaining in notes in the workbook which are the results.

**newdoverman** · 03-13-2014, 03:31 PM

I copied your snaps and created a workbook. Perhaps this is what you are looking for:

**newdoverman** · 03-13-2014, 05:12 PM

If I didn't make a mistake in translating the formula, this should be the equivalent using normal cell references:

Formula:

=IFERROR(INDEX(Accounts!$C$2:$C$10,MATCH(Checks!$V2,Accounts!$A$2:$A$10,0)),"")

When in a table, don't drag the formulae down, go to the last cell in the table (lower right) and hit the TAB key. This will insert a new row into the table and bring with it the formulae from the row above it. That is the advantage of tables over ranges.

**shtuamouth** · 03-13-2014, 05:35 PM

thanx for the formula newdoberman.

i myself first translated to this same formula but it is not working for me. dont know where i am going wrong.
i have attached the excel sheet of mine.

kindly let me know where i am going wrong. the formula is pasted there so everything is ready for you this time.

qwqwqw.xlsx

**newdoverman** · 03-13-2014, 05:52 PM

I fixed a couple of things. In the formula, the MATCH was referring to the wrong column and the values should have been numbers instead of text if you ever wanted to do calculations without problems. The formula was also in a column formatted as TEXT instead of GENERAL.

**shtuamouth** · 03-13-2014, 07:45 PM

thank you so much newdoverman. it worked.

God bless you

**newdoverman** · 03-13-2014, 07:59 PM

You're welcome. I'm happy that it worked for you.

**shtuamouth** · 03-14-2014, 05:16 PM

Hello newdoverman.

i have an assigment to submit tomorrow. may you kindly help me out in this. i have tried to setup files and explanations here to my best so to save your time.

There are 3 worksheets here. BillExpenses, BillPayments and PV.
I used data validation to create a drop down list in the PV worksheet under PV NO. the source has been used from worksheet BillExpenses. The source formula can be viewed in column K.
I need to fill in the headings called Account Title, A/C Code, Project Code, Debit and Credit in the PV template.
I have used vlookup formula to work on the PV template from BillExpenses worksheet.
I am stuck now at using the BillPayments worksheet.

Kindly see the pictures for a better understanding. I need to match Column C [BillID] of BillExpenses worksheet with that of Column A [ID] of BillPayments worksheet.
The BillPayment column A shows to sets of numbers where you can see it matches with one of Column C of BillExpenses worksheet.

I want to match not just the columns but to identify the similar text or number and then to return the values of that row where it matches (from the BillPayments worksheet) and post the values in the PV worksheet under my desired rows.

See PV picture to get better understanding and for the rest.

BillExpense1.jpg BillPayments.jpg PV.jpg

Kindly help me in this and I will be utmost grateful folks.
The excel file is also attached may you want to view the formulas used or to test it.

test.xlsx

a dropbox link is also available for this excel file. its as under:

https://www.dropbox.com/s/oyqhcoigd93osxh/test.xlsx

**newdoverman** · 03-14-2014, 07:36 PM

This should give you a start. There is a problem with your drop down list. It is showing an error of numbers stored as TEXT. This is a result of the formula that produces the numbers for the drop down list. They are TEXT numbers. To convert them to "real" numbers multiply the formula that creates the list by 1 or do some other math operation like +0. The formula then becomes:

Formula:



=IFERROR(INDEX($A$2:$A$120, MATCH(0, IF(LEN($A$2:$A$120), COUNTIF($K$1:$K1, $A$2:$A$120), 1), 0))*1, "")

enter with Ctrl + Shift + Enter

The formula for the name ABC can now be:

Formula:

=OFFSET(BillExpenses!$K$2, 0, 0, COUNT(BillExpenses!$K$2:$K$120))

This makes ABC a dynamic list.

I filled in part of the PV worksheet, the rest if needed, I think you can figure out.

The numbers in column A of the first worksheet were again TEXT. I clicked on the error message and converted the numbers to real numbers. Unless there is a reason to have numbers as TEXT leave them as NUMBERS by default.

**shtuamouth** · 03-15-2014, 03:02 PM

thank you so much newdoverman for helping me once again and taking out time to explain this much.

just one thing i want to know though i did not need it here but still am curious. where ever i applied the vlookup formula in the PV template it did not work and showed N/A instead. and why would one need to convert text numbers as real numbers. what is the benefit over it when i think i will get the same results or is it something that may cause problems afterwards.
the way i know to change the text numbers to real numbers is pressing Ctrl+1 on a cell and if it shows text change it to general. it this right ?

**newdoverman** · 03-15-2014, 04:16 PM

The N/A value not available. This was a problem with your drop down list in that some of the numbers in the drop down were text numbers and some were not and they didn't match up with the lookup values. Errors were also present when a text number was chosen from the drop down list. It still worked but why have errors when you can get rid of them at the source.

The reason for having numbers as "real" numbers as default is that you never know what your future requirements are going to be and where real numbers will be required....better to eliminate potential future problems early in development than to try and correct problems when it won't be so easy.

Ctrl + 1 will bring up the formatting dialogue box. Unfortunately it won't change a text number to a real number. Text numbers that I have seen have a green triangle in the upper left corner of the cell. If you click on the cell a yellow box with a ! in it shows up. Right click the yellow box and choose to convert to number. If several cells are selected, all cells will be converted at the same time.

**shtuamouth** · 03-15-2014, 04:31 PM

thank you so much for this useful information.

**newdoverman** · 03-15-2014, 04:50 PM

You're welcome.

Comparing two columns, and returning values in the 4th column from the 3rd column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Re: Comparing two columns, and returning values in the 4th column from the 3rd column

Thread Information

Users Browsing this Thread

Similar Threads

Matching values in 3 columns, same row, and returning a value in same row from 4th column

Comparing 2 columns and returning a value of a 3rd column

Matching values from 2 columns and returning the value in 3rd column

comparing values and returning different column

[SOLVED] Comparing values and returning the column heading

Bookmarks

Bookmarks

Posting Permissions