Looking for VLOOKUP Trick

shdawson · Dec 29, 2010

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?

Thanks,
S

shdawson · Jun 29, 2011

This is the sheet where the formula will live, in column A:

Excel Workbook

A

B

C

D

E

F

G

1

MSP

Project Manager

Tower

Workstream

Subject Area

Clarity #

2

ADW

Roadmap

0

3

ADW

Roadmap

0

4

ADW

Roadmap

0

5

ADW

Roadmap

0

6

x

ADW

Roadmap

0

7

ADW

Roadmap

47644

8

ADW

Roadmap

47644

9

ADW

Roadmap

47644

10

ADW

Roadmap

47646

11

ADW

Roadmap

47647

12

ADW

Roadmap

47649

Step 1 - Get Roadmap Entries

Excel 2003
This is the lookup sheet:

Excel Workbook

D

E

F

G

H

I

1

Project #

Control #

Tower

Workstream

Project

MSP

2

801

801.0

ADW

Roadmap

Enterprise Data Services

R

3

47644

47644.0

ADW

TD Remodel

Application End State

Y

Detail

Excel 2003

So, looking for a Y to go into A7, where A1-A6 has nothing to be found.

Thank You,
SHD

Matty · Jun 29, 2011

This works for me:

Code:

=IF(ISNA(MATCH(1,IF(Detail!$F$2:$F$1000=D2,IF(Detail!$D$2:$D$1000=G2,1)),0)),"",INDEX(Detail!$I$2:$I$1000,MATCH(1,IF(Detail!$F$2:$F$1000=D2,IF(Detail!$D$2:$D$1000=G2,1)),0)))

Remember to commit it with CTRL+SHIFT+ENTER.

Note: This will only work properly if 'Tower' and 'Project #', in combination, represent a unique reference.

Matty

shdawson · Jun 29, 2011

Nice formula edit.

The formula still will not calculate on the worksheet. It shows as the formula in the cell, not even #N/A in the cell.

So, when this happens, is the formula the problem or is there something else that is wrong that is preventing the formula from evaluating?

Thanks,
SHD

shdawson · Jun 30, 2011

Edit to the formula, from another person.

Code:

=IF(SUM((D2=Detail!$F2:F$1000)*(G2=Detail!$D2:$D$1000))=0,"",INDEX(Detail!$I$2:$I$1000,SUM((D2=Detail!$F2:F$1000)*(G2=Detail!$D2:$D$1000)*(ROW(Detail$I$1)))))

Same result, the formula is not processing. I see the formula in the cell.

Also, the curly brakets are not taking via C-S-E.

It could be how the external workbook is being referenced. I have these workbooks linked through Menu/Edit/Links.

Should the naming of the workbook being referenced change? Hard brackets or perhaps quotation marks of some kind?

Finally, not sure about the $I$1 part.

Thanks,
SHD

markmzz · Jun 30, 2011

Look at this sheets:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>MSP</TD><TD>Project Manager</TD><TD style="TEXT-ALIGN: right"></TD><TD>Tower</TD><TD>Workstream</TD><TD>Subject Area</TD><TD>Clarity #</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>=SE(SOMA((D2=Detail!$F$2:$F$3)</TD><TD></TD><TD></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47646</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47647</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47649</TD></TR></TBODY></TABLE>Master-Problem

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>MSP</TD><TD>Project Manager</TD><TD style="TEXT-ALIGN: right"></TD><TD>Tower</TD><TD>Workstream</TD><TD>Subject Area</TD><TD>Clarity #</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Y</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Y</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Y</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47646</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47647</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47649</TD></TR></TBODY></TABLE>Master-OK

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A2</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((D2=Detail!$F$2:$F$3)*(G2=Detail!$D$2:$D$3))=0,"",INDEX(Detail!$I$2:$I$3,SUM((D2=Detail!$F$2:$F$3)*(G2=Detail!$D$2:$D$3)*(ROW(Detail!$I$2:$I$3)-ROW(Detail!$I$1)))))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Project #</TD><TD>Control #</TD><TD>Tower</TD><TD>Workstream</TD><TD>Project</TD><TD>MSP</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">801</TD><TD style="TEXT-ALIGN: right">801.0</TD><TD>ADW</TD><TD>Roadmap</TD><TD>Enterprise Data Services</TD><TD>R</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD><TD style="TEXT-ALIGN: right">47644.0</TD><TD>ADW</TD><TD>TD Remodel</TD><TD>Application End State</TD><TD>Y</TD></TR></TBODY></TABLE>Detail

You must delete the column A and then add a new column A in the sheet Master-Problem.

How to:

Select the entire column A (giving a click in the header) and clicking with the right mouse button on the selection and choose Delete.

Then, select the entire new column A (former column B) and click the right mouse button again on the selection and choose Insert.

Markmzz

JAG836 · Jun 30, 2011

I'm still having problems with the N/A result

and am really hoping someone can help me out.

I've tried the suggestions below, checking to make sure the cells are formatted as numbers, checking for any hidden spaces before or after the number and all have not worked.

Since the HTML Maker add-in is currently unavailable, I can email my workbook to whoever is willing to help, or is there another way to post my file?

Any help would be GREATLY appreciated!

DonkeyOte said:
You mentioned VLOOKUP tricks - demo'd by Barry Houdini the other day

Code:

=VLOOKUP(A2,CHOOSE({1,2},'Tasks Report'!X$2:X$10000,'Tasks Report'!L$2:L$10000),2,0)

In terms of your error - the MATCH should be exact I suspect:

Code:

=INDEX('Tasks Report'!L$2:L$10000,MATCH(A2,'Tasks Report'!X$2:X$10000,0))

shdawson · Jun 30, 2011

Right.

The problem is this referencing out to the linked workbook. I enter your formula, Mark, and I am prompted to point to the workbook. I do so. It double enters

Current Formula:

Code:

=IF(SUM((C2=[Detail]Detail!$F$2:$F$1000)*(F2=[Detail]Detail!$D$2:$D$1000))=0,"",INDEX([Detail]Detail!$I$2:$I$1000,SUM((C2=[Detail]Detail!$F$2:$F$1000)*(F2=[Detail]Detail!$D$2:$D$3)*(ROW([Detail]Detail!$I$2:$I$1000)-ROW([Detail]Detail!$I$1)))))

I expanded the rows to 1000, in order to get all of the lookup table.

I tried to delete the Detail, both in parenthesis and with the exclamation point. I am still prompted to point to the workbook.

The end result is #N/A for all cells. It has to be how the external is being referenced, wtih the double statment of Detail.

I checked the link between the workbooks. The lookup table is fine, but it is trying to refernce Detail as a different file name. This is expected, with the double entry of Detail.

The curly brackets are in place, via C-S-E. HORRAY!

When I try to show calcualtion steps, Excel crashes. Probalby the double reference again.

So, if this referencing the linked worksheet can be resolved, I would say this is most likely done. Perhaps linking the worksheets is not the best approach...?

Thanks,
SHD

markmzz · Jun 30, 2011

Shdawson,

In my example, all the sheets (Master-Problem, Master-OK and Detail) are in the same Workbook (SHD2.xls).

And in yours? Give me more detail examples.

Markmzz

shdawson · Jun 30, 2011

OK, simple is better. Diagnose the problem, then seperate the worksheets as necessary later.

Copied the worksheet to the same workbook. Changed the worksheet reference.

Current Formula:

Code:

=IF(SUM((C2=Detail!$F$2:$F$1000)*(F2=Detail!$D$2:$D$1000))=0,"",INDEX(Detail!$I$2:$I$1000,SUM((C2=Detail!$F$2:$F$1000)*(F2=Detail!$D$2:$D$3)*(ROW(Detail!$I$2:$I$1000)-ROW(Detail!$I$1)))))

with curly brackets ala C-S-E.

I had this worksheet in a seperate workbook, and pointed to the lookup table. Excel then Linked the workbooks.

Still getting #N/A

When I right-click on a cell to Show Calcuation steps, Excel crashes...sends error report to Microsoft...and restarts Excel.

Perhaps I need to change the oil in this computer. LOL.

Hmmmmm......
S

shdawson · Jun 30, 2011

Current view of worksheet that needs the formula:

Excel Workbook

A

B

C

D

E

F

1

MSP

Project Manager

Tower

Workstream

Subject Area

Clarity #

2

#N/A

ADW

Roadmap

0

3

#N/A

ADW

Roadmap

0

4

#N/A

ADW

Roadmap

0

5

#N/A

ADW

Roadmap

0

6

#N/A

ADW

Roadmap

0

7

#N/A

ADW

Roadmap

47644

8

#N/A

ADW

Roadmap

47644

9

#N/A

ADW

Roadmap

47644

10

#N/A

ADW

Roadmap

47646

11

#N/A

ADW

Roadmap

47647

12

#N/A

ADW

Roadmap

47649

13

#N/A

ADW

Roadmap

47650

14

#N/A

ADW

Roadmap

47669

15

#N/A

ADW

Roadmap

47679

16

#N/A

ADW

Roadmap

47679

17

#N/A

ADW

Roadmap

47679

18

#N/A

ADW

Roadmap

47680

19

#N/A

ADW

Roadmap

47682

20

#N/A

ADW

Roadmap

48880

Step 1 - All Roadmap Entries

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Current view of worksheet that supplies that looked up data:

Excel Workbook

D

E

F

G

H

I

1

Project #

Control #

Tower

Workstream

Project

MSP

2

801

801.0

ADW

Roadmap

Enterprise Data Services

R

3

47644

47644.0

ADW

TD Remodel

Application End State

Y

Detail

Excel 2003

Looking for VLOOKUP Trick

shdawson

Active Member

shdawson

Active Member

Excel Facts

Matty

Well-known Member

shdawson

Active Member

shdawson

Active Member

markmzz

MrExcel MVP

JAG836

New Member

shdawson

Active Member

markmzz

MrExcel MVP

shdawson

Active Member

shdawson

Active Member

Similar threads

Forum statistics

Share this page

Looking for VLOOKUP Trick

Active Member

Active Member

Excel Facts

Well-known Member

Active Member

Active Member

MrExcel MVP

New Member

Active Member

MrExcel MVP

Active Member

Active 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