how to use lookup to get data by matching data in two sheets

Ref. attached TOFORUM.XLSX. There are 2 sheets Latest, BANK

work to be done at LATEST (columns H,I,J,K), based on data from BANK.

A formula is needed for columns H,I,J,K of sheet LATEST.

It should match Sl.No. (A column of both sheets) and fetch data such as Account Name, Bank, Branch, Ac No into columns H,I,J,K from Sheet BANK's corresponding columns.

Also, if there is no data available in Sheet BANK, it should leave the data without changes in Sheet Latest.

How to do this, pls help.

There are two things must be done:

1) Re attach file. I could not open it.

2) Change the title
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Originally Posted by bebo021999

There are two things must be done:

1) Re attach file. I could not open it.

2) Change the title
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ok Thanks i will follow.

I was able to open the sheet OK. However: your sample is MASSIVE. 10-20 rows would be much better than this.

"Also, if there is no data available in Sheet BANK, it should leave the data without changes in Sheet Latest." You cannot have a formula and a value in the same cell. You need to rethink your requirements.

Originally Posted by Glenn Kennedy

I was able to open the sheet OK. However: your sample is MASSIVE. 10-20 rows would be much better than this.
"Also, if there is no data available in Sheet BANK, it should leave the data without changes in Sheet Latest." You cannot have a formula and a value in the same cell. You need to rethink your requirements.

Thanks, a small file is attached, also i understood "You cannot have a formula and a value in the same cell. "

I could not open it, or I missed something?

Untitled.png

In H2, copied across and down:

=IFERROR(VLOOKUP($A2,BANK!$A$1:$G$24,COLUMNS($A:B),FALSE),"")

You will see that 2 has returned no results. This is because the number is stored as text. Re-enter the 2's as numbers and it works

i've just noticed that you're using excel 2003. I'll repost shortly. I'm just about to get off a bus...

Originally Posted by Glenn Kennedy

In H2, copied across and down:

=IFERROR(VLOOKUP($A2,BANK!$A$1:$G$24,COLUMNS($A:B),FALSE),"")

You will see that 2 has returned no results. This is because the number is stored as text. Re-enter the 2's as numbers and it works

Thanks, I will try and revert.

Originally Posted by Glenn Kennedy

i've just noticed that you're using excel 2003. I'll repost shortly. I'm just about to get off a bus...

GK, He/she is using 2013 Ver.

Dohh!! But (s)he is using the .xls extension....

Jilaba, do you need backwards compatability with Excel 2003??

Originally Posted by Glenn Kennedy

Dohh!! But (s)he is using the .xls extension....

Jilaba, do you need backwards compatability with Excel 2003??

No i do not want actually, but i am habited to save all excel files in 2003, for no reason.

I suggest that you switch. 2007+ (.xlsx) is FAR superior to 2003- (.xls). My solution should work fine in an xlsx.

But re-read the comment at Post 7...

Originally Posted by Glenn Kennedy

I suggest that you switch. 2007+ (.xlsx) is FAR superior to 2003- (.xls). My solution should work fine in an xlsx.

Yes I will switch to .xlsx soon.
Your formula is working, but i found some difficulties using that.

1. The formula uses both A and B columns of sheet "Latest". Is it supposed to be so. Can't we use A column only?
2. In my full workbook, i am unable to go beyond serial 25 (a column is serial). it gets no data. I attached a mid sized file as no other way to explain.

2. Modify the formula to read: =IFERROR(VLOOKUP($A2,BANK!$A$1:$B$240,COLUMNS($A:B),FALSE),"") That will extend the range being searched to just past the last row of data (228) on the BANK sheet.
1. The 'COLUMNS($A:B)' part of the formula is being used as a counter. If you select LATEST!D2 and run the Evaluate Formula function from the Formula tab you'll see that it returns the number 2 which instructs VLOOKUP to look in the second column. COLUMNS($A:A) would instruct VLOOKUP to look in the first column and return the Sl. No. instead of the name.
Let us know if you have any questions.

Originally Posted by JeteMc

2. Modify the formula to read: =IFERROR(VLOOKUP($A2,BANK!$A$1:$B$240,COLUMNS($A:B),FALSE),"") ...........

thanks a lot, it worked fine.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

thanks JeteMc.