Vlookup of one value but multiple results

E

Excel876

New Member
Joined
Jun 20, 2011
Messages
3
Thank you for reading my question. I hope someone can help me. How do I use vlookup function with the example below.

Vlookup six1 but gives me a multiple results not vlookup six1 but only take the first number which is 1234.90?


example:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>amount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Six1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1234.9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Six1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>345</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Six1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>76589</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>dainty</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>190</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Dainty</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>12</TD></TR></TBODY></TABLE>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Excel876 said:
Thank you for reading my question. I hope someone can help me. How do I use vlookup function with the example below.

Vlookup six1 but gives me a multiple results not vlookup six1 but only take the first number which is 1234.90?


example:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>name</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>amount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Six1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1234.9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Six1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>345</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Six1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>76589</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>dainty</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>190</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Dainty</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>12</TD></TR></TBODY></TABLE>
Click to expand...
Try this...

Book1
ABCDEF
1nameamount_LookupCountAmount
2Six11234.9_Six131234.9
3Six1345___345
4Six176589___76589
5dainty190___
6Dainty12___
Sheet1

Enter this formula in E2. This will return the count of records that coresspond to the lookup value.

=COUNTIF(A2:A6,D2)

Enter this formula in F2:

=IF(ROWS(F$2:F2)>E$2,"",INDEX(B$2:B$6,MATCH(D$2,A$2:A$6,0)+ROWS(F$2:F2)-1))

Copy down until you get blanks.
 
Upvote 0
Hi ,

Thank you T. Valko but I was wrong with my question , im sorrry but i hope you can still help me or anyone. i forgot to add that I have to get the values from another worksheet and compare that values if it matches.

e.g.
Worksheet 1: SaMPLE1

Name Amount
Six1 100
Six1 200
Six1 300
Six1 400
Baby0 ap123
Baby0 ab143

Now Worksheet 2: Sample 2

Name Amount Code RPU# Address
Six1 100 NON 1 345 College St
Six1 200 NON 1 345 College St
Six1 300 NON 1 345 College St

I am aiming to compare worksheet 1 and worksheet 2 (just name & amount) values are correct? What I'm having problem is when I vlookup for the name i'm okay but the amount it gives me the first number all the time though there are 3 values. The results is always like this,

Six1 100
Six1 100
Six1 100

Instead of ;
Six1 100
Six1 200
Six1 300

The index and match i think is only good if only one worksheet is involved. But in my case I'm comparing the values to another file and compare if matches or not. so if they change the amount on the other file, the other will be updated right away. I hope i didnt confuse anyone more. I hope i'm clear. Any help would be appreciated. Thanks.
 
Upvote 0
Excel876 said:
Hi ,

Thank you T. Valko but I was wrong with my question , im sorrry but i hope you can still help me or anyone. i forgot to add that I have to get the values from another worksheet and compare that values if it matches.

e.g.
Worksheet 1: SaMPLE1

Name Amount
Six1 100
Six1 200
Six1 300
Six1 400
Baby0 ap123
Baby0 ab143

Now Worksheet 2: Sample 2

Name Amount Code RPU# Address
Six1 100 NON 1 345 College St
Six1 200 NON 1 345 College St
Six1 300 NON 1 345 College St

I am aiming to compare worksheet 1 and worksheet 2 (just name & amount) values are correct? What I'm having problem is when I vlookup for the name i'm okay but the amount it gives me the first number all the time though there are 3 values. The results is always like this,

Six1 100
Six1 100
Six1 100

Instead of ;
Six1 100
Six1 200
Six1 300

The index and match i think is only good if only one worksheet is involved. But in my case I'm comparing the values to another file and compare if matches or not. so if they change the amount on the other file, the other will be updated right away. I hope i didnt confuse anyone more. I hope i'm clear. Any help would be appreciated. Thanks.
Click to expand...
What version of Excel are you using?
 
Upvote 0
I have pretty much the exact same problem. I am running Excel 2003. I would love to see what anybody can come up with. Thanks!
 
Upvote 0
philipsk said:
I have pretty much the exact same problem. I am running Excel 2003. I would love to see what anybody can come up with. Thanks!
Click to expand...
Maybe something like this.

Use another column and concatenate column A and column B like this:

Data set 1:

Book1
ABC
2Six1100Six1^100
3Six1200Six1^200
4Six1300Six1^300
5Six1400Six1^400
6Baby0ap123Baby0^ap123
7Baby0ab143Baby0^ab143
Sheet1

Formula entered in C2 and copied down:

=A2&"^"&B2

Data set 2:

Book1
ABC
10Six1100Six1^100
11Six1200Six1^200
12Six1300Six1^300
Sheet1

Formula entered in C10 and copied down:

=A10&"^"&B10

Then, with data set 1:

Book1
ABCD
2Six1100Six1^100OK
3Six1200Six1^200OK
4Six1300Six1^300OK
5Six1400Six1^400Not Found
6Baby0ap123Baby0^ap123Not Found
7Baby0ab143Baby0^ab143Not Found
Sheet1

This formula entered in D2 and copied down:

=IF(COUNT(MATCH(C2,C$10:C$12,0)),"OK","Not Found")
 
Upvote 0
Thank you for your help. I will keep trying. It's not what i want to happen. Does anyone bought a book here. I want to expose myself more on how to use formulas. I liked to buy a book that I can enhanced my skill and know more about different scenario. what would you recommend? By the way, I'm using 2007.

what i'm working on is matching values of two different file. I'm comparing the values of the other to another.if it matches then I'm getting ok if not i expect to get a "N/A" or "not found" comment. i was specifically told to use vlookup or a combination is okay. So i dont know, anyone? thanks
 
Upvote 0
Excel876 said:
Thank you for your help. I will keep trying. It's not what i want to happen. Does anyone bought a book here. I want to expose myself more on how to use formulas. I liked to buy a book that I can enhanced my skill and know more about different scenario. what would you recommend? By the way, I'm using 2007.

what i'm working on is matching values of two different file. I'm comparing the values of the other to another.if it matches then I'm getting ok if not i expect to get a "N/A" or "not found" comment. i was specifically told to use vlookup or a combination is okay. So i dont know, anyone? thanks
Click to expand...
If you're comparing 2 columns of data to 2 other columns of data VLOOKUP won't work.

Here's another way that doesn't use the column of concatenated data.

Book1
AB
2Six1100
3Six1200
4Six1300
Sheet2


Book1
ABC
2Six1200OK
3Six1300OK
4Six1400Not Found
5Baby0ap123Not Found
6Baby0ab143Not Found
Sheet1

THis array formula** entered in C2 and copied down:

=IF(COUNT(MATCH(1,IF(Sheet2!A$2:A$4=A2,IF(Sheet2!B$2:B$4=B2,1)),0)),"OK","Not Found")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

However, if you have 1000's of rows of data the concatenation method would be better (which is why I suggested it in the first place!).
 
Upvote 0
Excel876 said:
Hi ,

Thank you T. Valko but I was wrong with my question , im sorrry but i hope you can still help me or anyone. i forgot to add that I have to get the values from another worksheet and compare that values if it matches.

e.g.
Worksheet 1: SaMPLE1

Name Amount
Six1 100
Six1 200
Six1 300
Six1 400
Baby0 ap123
Baby0 ab143

Now Worksheet 2: Sample 2

Name Amount Code RPU# Address
Six1 100 NON 1 345 College St
Six1 200 NON 1 345 College St
Six1 300 NON 1 345 College St

I am aiming to compare worksheet 1 and worksheet 2 (just name & amount) values are correct? What I'm having problem is when I vlookup for the name i'm okay but the amount it gives me the first number all the time though there are 3 values. The results is always like this,

Six1 100
Six1 100
Six1 100

Instead of ;
Six1 100
Six1 200
Six1 300

The index and match i think is only good if only one worksheet is involved. But in my case I'm comparing the values to another file and compare if matches or not. so if they change the amount on the other file, the other will be updated right away. I hope i didnt confuse anyone more. I hope i'm clear. Any help would be appreciated. Thanks.
Click to expand...


You could insert a helper column in both sheets. Concatenate Columns A& B on both sheets in the helper column and then VLOOK-UP on the helper column.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,574
Messages
6,179,626
Members
452,933
Latest member
patv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top