Vlookup Help please!

D

Dcw0405

I'm needing help figuring out how exactly to return values in another table.
For example, I have a table with a column that holds the values "eBay" and
"PayPal". I want to create a new table that shows all the PayPal
transactions, along with the corresponding values in the next rows.

Here is the table I have...
PayPal Bus A Blue
eBay Train B Green
eBay Plane C Orange
PayPal Car D Black
eBay Truck E Red
PayPal Boat F Purple

And the table I want to create should look like this...
PayPal Bus A Blue
PayPal Car D Black
PayPal Boat F Purple


I tried this formula VLOOKUP("PayPal",$B$4:$E$9,2,FALSE) but when I do that
it just returns this table..
PayPal Bus A Blue
PayPal Bus A Blue
PayPal Bus A Blue

Anybody have any ideas?? Thanks!!
 
G

Glenn

Dcw0405 said:
I'm needing help figuring out how exactly to return values in another table.
For example, I have a table with a column that holds the values "eBay" and
"PayPal". I want to create a new table that shows all the PayPal
transactions, along with the corresponding values in the next rows.

Here is the table I have...
PayPal Bus A Blue
eBay Train B Green
eBay Plane C Orange
PayPal Car D Black
eBay Truck E Red
PayPal Boat F Purple

And the table I want to create should look like this...
PayPal Bus A Blue
PayPal Car D Black
PayPal Boat F Purple


I tried this formula VLOOKUP("PayPal",$B$4:$E$9,2,FALSE) but when I do that
it just returns this table..
PayPal Bus A Blue
PayPal Bus A Blue
PayPal Bus A Blue

Anybody have any ideas?? Thanks!!

Assuming your original table is in A1:D6, put PayPal in F1. Put the following
array formula (commit with CTRL+SHIFT+ENTER) in G1:

=IF(COUNTIF($A$1:$A$6,$F$1)>=ROW(A1),
SMALL(IF($A$1:$A$6=$F$1,ROW($A$1:$A$6),""),ROW()),"")

Adjust the ranges to match your actual data. Copy down until you get blanks.
Put this "normal" formula in H1 and copy down and across as needed:

=IF($G1<>"",INDEX(A$1:A$6,$G1),"")
 
G

Glenn

Glenn said:
Assuming your original table is in A1:D6, put PayPal in F1. Put the
following array formula (commit with CTRL+SHIFT+ENTER) in G1:

=IF(COUNTIF($A$1:$A$6,$F$1)>=ROW(A1),
SMALL(IF($A$1:$A$6=$F$1,ROW($A$1:$A$6),""),ROW()),"")

Adjust the ranges to match your actual data. Copy down until you get
blanks. Put this "normal" formula in H1 and copy down and across as needed:

=IF($G1<>"",INDEX(A$1:A$6,$G1),"")


Actually, I missed an "A1" in the first formula (still array-entered):

=IF(COUNTIF($A$1:$A$6,$F$1)>=ROW(A1),
SMALL(IF($A$1:$A$6=$F$1,ROW($A$1:$A$6),""),ROW(A1)),"")
 
Q

Qikslvr

I am having the same issue. The reason VLOOKUP doesn't work is becasue on the
second line it still finds the first instance of "PayPal" and returns the
appropriate response. I am working on limiting the range of the lookup but
have not gotten it working yet.
Heres where I am now
=IF(VLOOKUP(AG6,Lists!$B$6:$I$178,8,FALSE)=AH5,VLOOKUP(AG6,ADDRESS(MATCH(AH42,Lists!I:I),9):$I$178,8,FALSE),VLOOKUP(AG6,Lists!$B$6:$I$178,8,FALSE))

the theory is that if the VLOOKUP returns the same value as the row above
it, then it would limit the range to begin on the row below the previous
response, and if not it would simply perform the lookup normally.
If anyone can help correct the problem, please help.
 
L

L. Howard Kittle

Using these two formulas I think will do that, albeit a bit clumbsy. Both
are array entered... CTRL+SHIFT+ENTER

A1 is PayPal.
B1:B6 is PayPal and eBay as in your example.
C1:C6 is Bus,Train, Plane, Car, Truck.
D1:D6 is A, B, C, D, E, F.
E1:E6 is Blue, Green, Orange, Black, Red, Purple.
F1:F6 is 1, 2, 3, 4, 5, 6.

In G1 (on a single line) array-enter... .

=INDEX($C$1:$C$6,LARGE(($B$1:$B$6=$A$1)*ROW($A$1:$A$6),COUNTIF($B$1:$B$6,$A$1)+1-F1)) Pull down to G6. Should return Bus, Car, Boat, #NUM, #NUM, #NUM.Select BOTH H1 & I1 and array-enter =VLOOKUP(G1,$C$1:$E$6,{2,3},0)Pull down to G6.Column H should have A, D, F, #NUM, #NUM, #NUM.Column I should have Blue, Black, Purple, #NUM, #NUM, #NUM.Change A1 to eBay and see the new results.To make changes to the vlookup formula you will need to select BOTH cellsholding the formula, make the changes and again array-enter.You should be able to adapt this to your real data following the conceptshere.I do not fully understand the Index formula and I am remiss in that I do notremember from whom I got it. Appoligies to the author.HTHRegards,Howard"Dcw0405" <[email protected]> wrote in messageI'm needing help figuring out how exactly to return values in anothertable.> For example, I have a table with a column that holds the values "eBay" and> "PayPal". I want to create a new table that shows all the PayPal> transactions, along with the corresponding values in the next rows.>> Here is the table I have...> PayPal Bus A Blue> eBay Train B Green> eBay Plane C Orange> PayPal Car D Black> eBay Truck E Red> PayPal Boat F Purple>> And the table I want to create should look like this...> PayPal Bus A Blue> PayPal Car D Black> PayPal Boat F Purple>>> I tried this formula VLOOKUP("PayPal",$B$4:$E$9,2,FALSE) but when I dothat> it just returns this table..> PayPal Bus A Blue> PayPal Bus A Blue> PayPal Bus A Blue>> Anybody have any ideas?? Thanks!!>>
 
P

Pete

Dcw0405 said:
I'm needing help figuring out how exactly to return values in another table.
For example, I have a table with a column that holds the values "eBay" and
"PayPal". I want to create a new table that shows all the PayPal
transactions, along with the corresponding values in the next rows.

Here is the table I have...
PayPal Bus A Blue
eBay Train B Green
eBay Plane C Orange
PayPal Car D Black
eBay Truck E Red
PayPal Boat F Purple

And the table I want to create should look like this...
PayPal Bus A Blue
PayPal Car D Black
PayPal Boat F Purple


I tried this formula VLOOKUP("PayPal",$B$4:$E$9,2,FALSE) but when I do that
it just returns this table..
PayPal Bus A Blue
PayPal Bus A Blue
PayPal Bus A Blue

Anybody have any ideas?? Thanks!!
This solution requires no formulas at all.

1. Put a header on the row above each column of data - eg HEAD1 HEAD2 HEAD3
HEAD4

2. Move to the row below the last row of data, and shade the 4 cells below
the last set of data

3. Select the headers, all the data and the shaded row below, and click once
in the Name Box (the drop-down list above the "A" label of column A), and
type Table1 (this will give the highlighted range of cells the name "Table1")

4. Insert another blank worksheet into the workbook, or click the tab of an
existing spare (blank) one

5. Save the workbook

6. Click on a cell in column A of the blank sheet, eg A4 (or wherever you
want the top left hand corner of the new table to start)

7. From the menu, select Data | Import External Data | New Database Query,
and when the Choose Data Source box appears, click the Databases tab and
click once on Excel Files* and then OK

8. In the Select Workbook box that appears, scroll down the list on the left
to find your current workbook (it should be there, but if not, use the window
on the right to navigate to the correct drive and folder), and select it,
then click OK

9. The Query Wizard - Choose Columns box will appear, and Table1 should be
highlighted in the left hand section. Click on the > sign in the central
column, and all of the column headings of your table should appear in the
right hand section. Click Next

9. In the next screen, click the heading under Columns to Filter that
corresponds to the PayPal, etc column in your table (HEAD1 in my example).
Then, in the next section to the right, select "equals" from the list box,
and in the next section to the right of that select "PayPal" from the list
box. Click Next.

10. You can choose any sort options you want, or none, then click Next

11. On the Finish screen, click Finish, then click OK on the Import Data
window.

The query wizard will create a new table with the data from your first
table, filtered by your choices. If you right click the first heading of the
new table, you should get options that include Edit Query and Refresh Data.

12. The reason for the shaded row at the bottom of the original table? It
graphically defines the end of the range "Table1", and if you add extra data
rows ABOVE this shaded area, they will automatically be include in the range
called Table1. When you refresh the new table, any new appropriate data will
be included.

Note that your new table can be named in the same way that you named Table1,
and even when the spreadsheet is saved and closed, you can use the same
process from another spreadsheet to access the named tables, and import data
from them into a new table ...

Welcome to the world of SQL queries!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top