Lookup 2 column of data

c1steady · Jun 8, 2011

Hi
I want to Lookup 2 column of data and output them in a single row.

see the link for better explanation, and Thanks in advance.

http://imageshack.us/photo/my-images/26/61187501.jpg/

texasalynn · Jun 9, 2011

try this

Excel Workbook

A

B

C

D

E

1

Raw data

2

No

Category

Value

Total

*

11

A003

Amount

100

*

A001

Amount

80

1

*

12

A003

Temp

A

*

Pressure

P

1

*

13

*

Print

10

1

*

14

*

Temp

30

1

*

15

*

A002

Amount

70

1

*

16

*

Pressure

50

1

*

17

*

Print

90

1

*

18

*

Temp

10

1

*

19

*

A003

Amount

100

1

*

20

*

Temp

A

1

*

21

*

Sheet1

The formula in E1 counts the number of Unique No's.
The formula in E3 copied down returns those Unique No's.
The formula in F3 is for ALL versions of Excel.
The formula in F4 is for 2007-2010 ONLY.
The data in A3:C12 is in a Table,
this means when you add new data to the bottom of the Table ALL the ranges in the ALL the formulas will update (expand).

E9:G20 is a Pivot Table created from the Raw Data.

Check out these links....

http://www.youtube.com/user/ExcelIsFun#p/search/2/DLgnhPSOEOw
http://www.youtube.com/user/ExcelIsFun#p/search/11/0QNznFt0El4
http://support.microsoft.com/kb/214142

I hope this helps.

Good luck.

Ak

c1steady · Jun 9, 2011

Thanks texasalynn & Akashwani. You guys are wonderful. I will need some time to review and comment.
Thanks once again

jasonb75 · Jun 10, 2011

Another alternative

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 62px"><COL style="WIDTH: 43px"><COL style="WIDTH: 63px"><COL style="WIDTH: 57px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=5>Raw Data</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-WEIGHT: bold">No</TD><TD style="FONT-WEIGHT: bold">Category</TD><TD style="FONT-WEIGHT: bold">Value</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="COLOR: #333333">A001</TD><TD>Print</TD><TD style="TEXT-ALIGN: right">10</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="COLOR: #333333">A001</TD><TD>Temp</TD><TD style="TEXT-ALIGN: right">30</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="COLOR: #333333">A001</TD><TD>Pressure</TD><TD>P</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="COLOR: #333333">A001</TD><TD>Amount</TD><TD style="TEXT-ALIGN: right">80</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="COLOR: #333333">A002</TD><TD>Temp</TD><TD style="TEXT-ALIGN: right">10</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="COLOR: #333333">A002</TD><TD>Pressure</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="COLOR: #333333">A002</TD><TD>Amount</TD><TD style="TEXT-ALIGN: right">70</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="COLOR: #333333">A002</TD><TD>Print</TD><TD style="TEXT-ALIGN: right">90</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="COLOR: #333333">A003</TD><TD>Amount</TD><TD style="TEXT-ALIGN: right">100</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="COLOR: #333333">A003</TD><TD>Temp</TD><TD>A</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="BACKGROUND-COLOR: #ffff00; COLOR: #333333; FONT-WEIGHT: bold" colSpan=5>Answer:</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold">Print</TD><TD style="FONT-WEIGHT: bold">Temp</TD><TD style="FONT-WEIGHT: bold">Pressure</TD><TD style="FONT-WEIGHT: bold">Amount</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="COLOR: #333333; FONT-WEIGHT: bold">A001</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">P</TD><TD style="TEXT-ALIGN: right">80</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="COLOR: #333333; FONT-WEIGHT: bold">A002</TD><TD style="TEXT-ALIGN: right">90</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">70</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="COLOR: #333333; FONT-WEIGHT: bold">A003</TD><TD> </TD><TD style="TEXT-ALIGN: right">A</TD><TD> </TD><TD style="TEXT-ALIGN: right">100</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B17</TD><TD>{=IFERROR(LOOKUP(2,1/IF($A$3:$A$12=$A17,$B$3:$B$12=B$16),$C$3:$C$12),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Akashwani · Jun 10, 2011

Hi Jason,

That is great, could you please explain how it works?

Thanks.

Ak

jasonb75 · Jun 10, 2011

Look at the logical test first,

IF($A$3:$A$12=$A17,$B$3:$B$12=B$16)

Both the test, and the TRUE result return an array or TRUE / FALSE results, effectively the same as 2 arrays of a sumproduct formula.

1/TRUE = 1 1/FALSE = #DIV/0! So you now have an array of {#DIV/0!,1,#DIV/0!,...

Lookup finds the last value < = 2 (ignoring the #DIV/0! errors) in the array, no match results in #N/A which is blanked out by IFERROR.

Lookup 2 column of data

c1steady

New Member

Excel Facts

texasalynn

Well-known Member

Akashwani

Well-known Member

c1steady

New Member

jasonb75

Well-known Member

Akashwani

Well-known Member

jasonb75

Well-known Member

Similar threads

Forum statistics

Share this page

Lookup 2 column of data

c1steady

New Member

Excel Facts

texasalynn

Well-known Member

Akashwani

Well-known Member

c1steady

New Member

jasonb75

Well-known Member

Akashwani

Well-known Member

jasonb75

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock