VLOOKUP between two sheets including entire row

[samdahlr]

Hello,

I have a single column of values as well as a table with a column that includes those same values but also many more. I want to look up the list of values I have in the table and then see the entire row in the table for each value I looked up. I want to be able to look up all the values at one as there are about 100,000 of them. Any thoughts? Help is much appreciated.

Thank you.

[Speshul]

So, you have a small list in one column of A B C D, and a table that includes the whole alphabet in the first column, but other data associated to each letter as well. You want to see the other data associated when you lookup from the single column of A B C D?

Can you use one vlookup per column and get the whole row of data that way?

=vlookup(a1,$c$2:$h$100000,COLUMNS($A$1:A1)+1,False)

Copied all the way across?

[samdahlr]

I have a huge database with columns such as address, name, phone number, license number etc.. and then I have on list of license numbers. I want to look up those license numbers in the table and be able to see the entire row for each number I look up. The table has about 200,000 rows and the list has about 100,000 values.

[Special-K]

VLOOKUP or MATCH may provide solutions.

Prob best post an example spreadsheet with a some test data.

[TheCman81]

Does this do what you need? If not can you supply some sample data?

samv1.xlsx

[samdahlr]

I am new to this program so I am not sure if the attachment is on here now. I attached sample data with the table and the column of values. I need to look up the column of NPI Numbers in the table. You should be able to see the field "NPI Number (National Provider Identifier)" in the table. When the values are returned I want their entire row to be returned as well so that I can compare the rows.

Thanks

[samdahlr]

Basically I am trying to dedupe the contacts but instead of just deleting all duplicate rows and keeping one random row, I need to keep the row that contains the most information for that contact.

[samdahlr]

I am not sure how to go about applying this formula to my data. Could you please explain?

[oeldere]

=iferror(VLookup(A2,table!A2:AF1078,2.0),"")

[FDibbins]

Hi.

I have removed your file, it looked like it had personal info in it (email addy, phone numbers etc) It is never a good idea to post info of that nature on a public forum

[FDibbins]

@ oeldere, I think your formula is missing a few things?

maybe...
=iferror(VLookup(A2,table!$A$2:$AF$1078,2,0),"")

[oeldere]

I already changed the code. Thanks anyway.

Please Login or Register  to view this content.

[FDibbins]

Sam, if you intend using column R on Table "NPI Number (npi)", I see you have multiple duplicates in that column, and in most of the other columns, the data seems to match, but not all columns. How do you intend handling that?

[UR TaxFriend]

PROBLEM:

I am trying to take the reading(s) from all three types of gas (D, H, M) and convert these figures from inches to gallons. The result then would go into Column(s) D, I, N (on appropriate row).

I created a vlook table on a separate sheet, because I have to do this monthly.

I also have a Monthly Template sheet (Mnth Temp) with no figures or formula.

Any help world be greatly appreciated.
Thank you in advance. Shari

[FDibbins]

UR Taxfriend, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[UR TaxFriend]

Dear Sir, my apologies, this was my first/last forum attempt. I tried and tried to start a new thread. When I was unsuccessful time after time, I got so fustrated. I thought if I posted anywhere then hopefully someone would guide me. That was not the case here. In stead I receive a reply stating I had broken some GOLDEN rule. Heaven for bid someone makes an error. I will be removing my user information tomorrow.

Fortunately I was able to resolve my question on my own.
Again my apologies for my error.

Sincerely
Ur Taxfriend

[samdahlr]

If the data does not match between two contacts I can look up the correct data in another database. I just need a fast way to do all of this due to their being so many contacts.

[samdahlr]

@FDibbins any more suggestions?

[FDibbins]

I removed your workbook because it had personal info in it. If you can remove those parts, then upload the WB again

[samdahlr]

Yes, my mistake.

Here is a much smaller sample set of my situation. If you can show me how to look up the Phone Numbers in sheet 2 in the table in sheet 1 and how the rest of the row for each phone number looked up align with the returned value that would be great! I am not very advanced with excel so you might have to go into detail a little. Thank you for your help.

[oeldere]

Based on your example, you have to use a index / match formula.

b2 =

Please Login or Register  to view this content.

This is another example as you earlier provided.

In #9 I gave you this formula to solve the problem.

Please Login or Register  to view this content.

[samdahlr]

When I enter the first formula in B2 of the List sheet it says #N/A

[FDibbins]

OK assuming you want to have the info from Table, populate List, then 1st, copy the headings across from Table to List. Then use this, copied down and across...
=IFERROR(INDEX(Table!$A$2:$B$6,MATCH(List!$A2,Table!$C$2:$C$6,0),MATCH(List!B$1,Table!$A$1:$B$1,0)),"")

[oeldere]

Please Login or Register  to view this content.

It works by me, see the attached file.

[samdahlr]

@FDibbins

So if I have 7000 rows in the table should I change where both of the 6's in the formula to be 7000?

[oeldere]

Please Login or Register  to view this content.

[samdahlr]

This formula worked when I used it in the sample data but for some reason with the real data it is giving me #REF! In each cell

[FDibbins]

@FDibbins

So if I have 7000 rows in the table should I change where both of the 6's in the formula to be 7000?

Yes, that is correct

=IFERROR(INDEX(Table!$A$2:$B$7000,MATCH(List!$A2,Table!$C$2:$C$7000,0),MATCH(List!B$1,Table!$A$1:$B$1,0)),"")

[samdahlr]

Okay. Now my only problem is that when I copy and paste the formula to B2 it automatically pulls up a window for me to open a file. I am not sure why it is doing this?

[FDibbins]

That is probably because the sheet names in the formula do not match your actual sheet names. Check that they are the same and adjust in the formula as needed