VLOOKUP - Multiple Criteria

G

gregula82

New Member
Joined
Aug 18, 2006
Messages
8
Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.
 
Sort by date Sort by votes
@Aladin

Apologies for the repeat.

I will try to simplify my question, I am trying to populate WD1 planned hours and WD1 planned cost on sheet 2 from sheet 1

sample data on Wd1 Sheet:

[TABLE="width: 480"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Programme ID[/TD]
[TD]Project ID[/TD]
[TD]Resource Name[/TD]
[TD]Task Name[/TD]
[TD]WD1 PH[/TD]
[TD]WD1 PC[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]201[/TD]
[TD]P268[/TD]
[TD]Name 1[/TD]
[TD]T1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]202[/TD]
[TD]P298[/TD]
[TD]Name 2[/TD]
[TD]T2[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]203[/TD]
[TD]P299[/TD]
[TD]Name 3[/TD]
[TD]T3[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]201[/TD]
[TD]P268[/TD]
[TD]Name 4[/TD]
[TD]T1[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]224[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD]P269[/TD]
[TD]Name 1[/TD]
[TD]T2[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]201[/TD]
[TD]P269[/TD]
[TD]Name 2[/TD]
[TD]T3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]202[/TD]
[TD]P268[/TD]
[TD]Name 3[/TD]
[TD]T1[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]336[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]201[/TD]
[TD]P299[/TD]
[TD]Name 4[/TD]
[TD]T4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]202[/TD]
[TD]P268[/TD]
[TD]Name 5[/TD]
[TD]T1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]201[/TD]
[TD]P298[/TD]
[TD]Name 6[/TD]
[TD]T3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]203[/TD]
[TD]P268[/TD]
[TD]Name 1[/TD]
[TD]T2[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]448[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]203[/TD]
[TD]P267[/TD]
[TD]Name 2[/TD]
[TD]T2[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]392[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]204[/TD]
[TD]P268[/TD]
[TD]Name4[/TD]
[TD]T5[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]392[/TD]
[/TR]
</tbody>[/TABLE]
Sample Data on Sheet2

[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Month[/TD]
[TD="class: xl65, width: 64"]Programme ID[/TD]
[TD="class: xl65, width: 64"]Project ID[/TD]
[TD="class: xl65, width: 64"]Resource Name[/TD]
[TD="class: xl65, width: 64"]Task Name[/TD]
[TD="class: xl65, width: 64"]PH[/TD]
[TD="class: xl65, width: 64"]PC[/TD]
[TD="class: xl65, width: 64"]WD1 PH[/TD]
[TD="class: xl65, width: 64"]WD1 PC[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 1[/TD]
[TD="class: xl65"]T1[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P298[/TD]
[TD="class: xl65"]Name 2[/TD]
[TD="class: xl65"]T2[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]203[/TD]
[TD="class: xl65"]P299[/TD]
[TD="class: xl65"]Name 3[/TD]
[TD="class: xl65"]T3[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 4[/TD]
[TD="class: xl65"]T1[/TD]
[TD="class: xl65, align: right"]28[/TD]
[TD="class: xl65, align: right"]224[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]224[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P269[/TD]
[TD="class: xl65"]Name 1[/TD]
[TD="class: xl65"]T2[/TD]
[TD="class: xl65, align: right"]35[/TD]
[TD="class: xl65, align: right"]280[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P269[/TD]
[TD="class: xl65"]Name 2[/TD]
[TD="class: xl65"]T3[/TD]
[TD="class: xl65, align: right"]35[/TD]
[TD="class: xl65, align: right"]280[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 3[/TD]
[TD="class: xl65"]T1[/TD]
[TD="class: xl65, align: right"]42[/TD]
[TD="class: xl65, align: right"]336[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]336[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P299[/TD]
[TD="class: xl65"]Name 4[/TD]
[TD="class: xl65"]T4[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 5[/TD]
[TD="class: xl65"]T1[/TD]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P298[/TD]
[TD="class: xl65"]Name 6[/TD]
[TD="class: xl65"]T3[/TD]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]203[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 1[/TD]
[TD="class: xl65"]T2[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="class: xl65, align: right"]448[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]448[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]203[/TD]
[TD="class: xl65"]P267[/TD]
[TD="class: xl65"]Name 2[/TD]
[TD="class: xl65"]T2[/TD]
[TD="class: xl65, align: right"]49[/TD]
[TD="class: xl65, align: right"]392[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]392[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]204[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 4[/TD]
[TD="class: xl65"]T5[/TD]
[TD="class: xl65, align: right"]49[/TD]
[TD="class: xl65, align: right"]392[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]392[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]229[/TD]
[TD="class: xl65"]P290[/TD]
[TD="class: xl65"]Name 7[/TD]
[TD="class: xl65"]T5[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P298[/TD]
[TD="class: xl65"]Name 1[/TD]
[TD="class: xl65"]T4[/TD]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 576"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

I am looking for a formula to populate column H and I with values from sheet 1 columns F and G when a match is found. if not populate #N/A.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi Aladain,

Hi Aladin,

The value from the combination of the following five columns should match.

[TABLE="width: 320"]
<colgroup><col span="5" width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Month[/TD]
[TD="class: xl65, width: 64"]Programme ID[/TD]
[TD="class: xl65, width: 64"]Project ID[/TD]
[TD="class: xl65, width: 64"]Resource Name[/TD]
[TD="class: xl65, width: 64"]Task Name[/TD]
[/TR]
</tbody>[/TABLE]

In other words,
If [(Sheet1.A2=Sheet2.A2)And((Sheet1.B2=Sheet2.B2)And(Sheet1.C2=Sheet2.C2)And(Sheet1.D2=Sheet2.D2)And(Sheet1.E2=Sheet2.E2)=True]
then sheet2.H2 should have the value from Sheet1.F1 and sheet2.I2 should have the value from Sheet1.G2.

Hope I am making sense. Apologies for any confusion and thanks for your time in looking into it. Much appreciated.
 
Upvote 0
ramgouda said:
Hi Aladain,

Hi Aladin,

The value from the combination of the following five columns should match.

[TABLE="width: 320"]
<TBODY>[TR]
[TD="class: xl65, width: 64"]Month
[/TD]
[TD="class: xl65, width: 64"]Programme ID
[/TD]
[TD="class: xl65, width: 64"]Project ID
[/TD]
[TD="class: xl65, width: 64"]Resource Name
[/TD]
[TD="class: xl65, width: 64"]Task Name
[/TD]
[/TR]
</TBODY>[/TABLE]

In other words,
If [(Sheet1.A2=Sheet2.A2)And((Sheet1.B2=Sheet2.B2)And(Sheet1.C2=Sheet2.C2)And(Sheet1.D2=Sheet2.D2)And(Sheet1.E2=Sheet2.E2)=True]
then sheet2.H2 should have the value from Sheet1.F1 and sheet2.I2 should have the value from Sheet1.G2.

Hope I am making sense. Apologies for any confusion and thanks for your time in looking into it. Much appreciated.
Click to expand...

Wd1, H2, copied down:

=A2&"|"&B2&"|"&C2&"|"&D2&"|"&E2

Sheet2, H2, copied across, and down:

=INDEX(Wd1!F:F,MATCH(A$2&"|"&$B2&"|"&$C2&"|"&$D2&"|"&$E2,Wd1!$H:$H,0))
 
Upvote 0
Another way:

Layout

[TABLE="width: 339"]
<colgroup><col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1389;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;" span="2"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <tbody>[TR]
[TD="width: 38, bgcolor: transparent"]Month[/TD]
[TD="width: 74, bgcolor: transparent"]Programme ID[/TD]
[TD="width: 53, bgcolor: transparent"]Project ID[/TD]
[TD="width: 82, bgcolor: transparent"]Resource Name[/TD]
[TD="width: 58, bgcolor: transparent"]Task Name[/TD]
[TD="width: 27, bgcolor: transparent"]PH[/TD]
[TD="width: 27, bgcolor: transparent"]PC[/TD]
[TD="width: 47, bgcolor: transparent"]WD1 PH[/TD]
[TD="width: 44, bgcolor: transparent"]WD1 PC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]T1[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow, align: right"]14[/TD]
[TD="bgcolor: yellow, align: right"]112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P298[/TD]
[TD="bgcolor: transparent"]Name 2[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow, align: right"]112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]P299[/TD]
[TD="bgcolor: transparent"]Name 3[/TD]
[TD="bgcolor: transparent"]T3[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow, align: right"]14[/TD]
[TD="bgcolor: yellow, align: right"]112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 4[/TD]
[TD="bgcolor: transparent"]T1[/TD]
[TD="bgcolor: transparent, align: right"]28[/TD]
[TD="bgcolor: transparent, align: right"]224[/TD]
[TD="bgcolor: yellow, align: right"]28[/TD]
[TD="bgcolor: yellow, align: right"]224[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P269[/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[TD="bgcolor: transparent, align: right"]35[/TD]
[TD="bgcolor: transparent, align: right"]280[/TD]
[TD="bgcolor: yellow, align: right"]35[/TD]
[TD="bgcolor: yellow, align: right"]280[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P269[/TD]
[TD="bgcolor: transparent"]Name 2[/TD]
[TD="bgcolor: transparent"]T3[/TD]
[TD="bgcolor: transparent, align: right"]35[/TD]
[TD="bgcolor: transparent, align: right"]280[/TD]
[TD="bgcolor: yellow, align: right"]35[/TD]
[TD="bgcolor: yellow, align: right"]280[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 3[/TD]
[TD="bgcolor: transparent"]T1[/TD]
[TD="bgcolor: transparent, align: right"]42[/TD]
[TD="bgcolor: transparent, align: right"]336[/TD]
[TD="bgcolor: yellow, align: right"]42[/TD]
[TD="bgcolor: yellow, align: right"]336[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P299[/TD]
[TD="bgcolor: transparent"]Name 4[/TD]
[TD="bgcolor: transparent"]T4[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow, align: right"]14[/TD]
[TD="bgcolor: yellow, align: right"]112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 5[/TD]
[TD="bgcolor: transparent"]T1[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: yellow, align: right"]7[/TD]
[TD="bgcolor: yellow, align: right"]56[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P298[/TD]
[TD="bgcolor: transparent"]Name 6[/TD]
[TD="bgcolor: transparent"]T3[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: yellow, align: right"]7[/TD]
[TD="bgcolor: yellow, align: right"]56[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent, align: right"]448[/TD]
[TD="bgcolor: yellow, align: right"]56[/TD]
[TD="bgcolor: yellow, align: right"]448[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]P267[/TD]
[TD="bgcolor: transparent"]Name 2[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[TD="bgcolor: transparent, align: right"]49[/TD]
[TD="bgcolor: transparent, align: right"]392[/TD]
[TD="bgcolor: yellow, align: right"]49[/TD]
[TD="bgcolor: yellow, align: right"]392[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]204[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 4[/TD]
[TD="bgcolor: transparent"]T5[/TD]
[TD="bgcolor: transparent, align: right"]49[/TD]
[TD="bgcolor: transparent, align: right"]392[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]229[/TD]
[TD="bgcolor: transparent"]P290[/TD]
[TD="bgcolor: transparent"]Name 7[/TD]
[TD="bgcolor: transparent"]T5[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P298[/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]T4[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]******[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]*********[/TD]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]****[/TD]
[TD="bgcolor: transparent"]****[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[/TR]
</tbody>[/TABLE]

Formula

Code: 
In H2 - use Ctrl+Shift+Enter to enter the formula

=VLOOKUP($E2,IF('Wd1'!$A$2:$A$14=$A2,IF('Wd1'!$B$2:$B$14=$B2,IF('Wd1'!$C$2:$C$14=$C2,IF('Wd1'!$D$2:$D$14=$D2,'Wd1'!$E$2:$G$14)))),MATCH(H$1,'Wd1'!$E$1:$G$1,0),0)

And copy to the right and down.

Markmzz
 
Upvote 0
Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!
 
Upvote 0
ScottVP said:
Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!
Click to expand...

If VLOOKUP is expected to return a text value when successful...
Rich (BB code): 
=IFERROR(LOOKUP(REPT("z",255),
  CHOOSE({1,2},VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
   VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0))),"")
If VLOOKUP is expected to return a number when successful...
Rich (BB code): 
=IFERROR(LOOKUP(9.99999999999999E+307,
  CHOOSE({1,2},VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
   VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0))),"")

Hope this helps.
 
Upvote 0
ScottVP said:
Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!
Click to expand...

Hi ScottVP,

If I understand correctly what you want, maybe this can helps:

Code: 
=IFERROR(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
IFERROR(VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),""))

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
197
Rokcmd
R
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
318
acool
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
628
Peter_SSs
Peter_SSs
C
  • Question Question
How to show a list view of columns based off another sheet / alternative to Pivot table?
Replies
7
Views
129
Domenic
D
J
  • Solved
Filter function with multiple and|or criteria not working
Replies
3
Views
588
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,833
Messages
6,181,242
Members
453,026
Latest member
cknader

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