Vlookup issue

[saltytuna]

Hi there

I'm having a rather large issue trying lookup on some data that I have, as I have 7 columns that need to be looked at before returning an account reference number.

I have data going from A2:G2 (multiple loan ref numbers) and in column H2, going vertically, I have the corresponding account reference number that covers all of the individual loan references.

I want to be able to use a function/formula that will look at a value in a different sheet, and compare to A2:G2, and if it's there, return the H2 account reference number, but I just cannot seem to find a way, as I have never come across this issue.

Please can someone help?

Thanks

Dee

[Pete_UK]

You can use this in B2 of Sheet1:

=IFERROR(VLOOKUP(A2,Sheet2!A:H,8,0),IFERROR(VLOOKUP(A2,Sheet2!B:H,7,0),IFERROR(VLOOKUP(A2,Sheet2!C:H,6,0),IFERROR(VLOOKUP(A2,Sheet2!D:H,5,0),IFERROR(VLOOKUP(A2,Sheet2!E:H,4,0),IFERROR(VLOOKUP(A2,Sheet2!F:H,3,0),IFERROR(VLOOKUP(A2,Sheet2!G:H,2,0),"Not found")))))))

However, none of the examples in column A have a match with those listed in Sheet2, so you will get "Not found" for them all.

Hope this helps.

Pete

[Pete_UK]

My reply doesn't seem to have registered on the New Posts list on in my UserCP.

Pete

[AZ-XL]

This is just an alternative solution

b2 = =IFERROR(INDIRECT(""&ADDRESS(MAX(COUNTIF(A2,Sheet2!$A$2:$G$19)*ROW(Sheet2!$A$2:$A$19)),8,4,,"Sheet2")&""),"Not Found")

Then ctrl+Shift+Enterş

[AZ-XL]

This is just an alternative solution

b2 = =IFERROR(INDIRECT(""&ADDRESS(MAX(COUNTIF(A2,Sheet2!$A$2:$G$19)*ROW(Sheet2!$A$2:$A$19)),8,4,,"Sheet2")&""),"Not Found")

Then ctrl+Shift+Enterş

[saltytuna]

This is just an alternative solution

b2 = =IFERROR(INDIRECT(""&ADDRESS(MAX(COUNTIF(A2,Sheet2!$A$2:$G$19)*ROW(Sheet2!$A$2:$A$19)),8,4,,"Sheet2")&""),"Not Found")

Then ctrl+Shift+Enterş

Thank you will give this a try also!

[saltytuna]

Hi Pete

My replies, don't seem to be coming through either! Thank you so much for taking the time to help me though!

I tried to incorporate your formula into the actual sheets:

=IFERROR(VLOOKUP(A2,'[Account numbers and Loan note numbers.xlsx]Account numbers and Loan note n'!A1:H1,8,0),IFERROR(VLOOKUP(A2,'[Account numbers and Loan note numbers.xlsx]Account numbers and Loan note n'!B1:H1,7,0),IFERROR(VLOOKUP(A2,'[Account numbers and Loan note numbers.xlsx]Account numbers and Loan note n'!C1:H1,6,0),IFERROR(VLOOKUP(A2,'[Account numbers and Loan note numbers.xlsx]Account numbers and Loan note n'!D1:H1,5,0), IFERROR(VLOOKUP(A2,'[Account numbers and Loan note numbers.xlsx]Account numbers and Loan note n'!E1:H1,4,0), IFERROR(VLOOKUP(A2,'[Account numbers and Loan note numbers.xlsx]Account numbers and Loan note n'!F1:H1,3,0),IFERROR(VLOOKUP(A2,'[Account numbers and Loan note numbers.xlsx]Account numbers and Loan note n'!G1:H1,2,0), "Not Found")))))))

But I just can't get it to work...what am I doing wrong?

Dee

[Pete_UK]

You have used A1:H1 as the table for the first VLOOKUP (and for subsequent ones) - it should be A:H (i.e. the full columns). You can just do Find&Replace (CTRL-H) on that cell and change 1 to nothing. Your formula assumes that the file [Account numbers and Loan note numbers.xlsx] is open at the same time.

Hope this helps.

Pete

[saltytuna]

Pete! You are a genius! Thank you so so much - now I shall have to try and understand the formula!!

It works a charm

Dee

[Pete_UK]

Glad to hear it, Dee.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete