VLOOKUP for a Dynamic Data Lookup Range

[sanmisds1]

Hello,

In Sheet1, I have the following:

Contract# ColA
ABC
BCD
CDE
.......

In Sheet2, I have the following:

Category ColA Contract# ColB ContractValue$ ColC
A ABC 1000
A BCD 1100
A CDE 1200
B ABC 1500
B BCD 1600
B CDE 1700
B ABC 1800
C BCD 2100
C CDE 2200

I would like a vlookup based on value in colA from Sheet1, lookup the data range from Sheet2 where Category, ColA, is 'A' or 'B' or 'C', etc., look for the corresponding Contract# in ColB, and if there is a match, return value from ColC (ContractValues$). Please note that in Sheet2, in ColB, there may be same contract# listed multiple times based on different Categories with different ContractValues$. I really hope you can help me. Really appreciate in advance. Thanks!

 

[sanmisds1]

Any one, please?

Thanks.

S

 

[Aladin Akyurek]

What is the output that you want to see in Sheet1 regarding ABC in A2?

 

[sanmisds1]

Hi Aladin,

For a Category 'A' and Contract# 'ABC', I'd like to see the output of 1000. I hope this answers your question.

Thank you.

S

 

[Aladin Akyurek]

A:C of Sheet2 houses the data.

Sheet1 (processing)...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]Contract#[/td][td]A[/td][td]B[/td][td]C[/td][/tr]
[tr][td]

2​

[/td][td]ABC[/td][td]

1000​

[/td][td]

1500​

[/td][td]

#N/A​

[/td][/tr]
[tr][td]

3​

[/td][td]BCD[/td][td]

1100​

[/td][td]

1600​

[/td][td]

2100​

[/td][/tr]
[tr][td]

4​

[/td][td]CDE[/td][td]

1200​

[/td][td]

1700​

[/td][td]

2200​

[/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In B2 control+shift+enter, not just enter, copy across, and down:

=INDEX(Sheet2!$C$2:$C$10,MATCH($A2,IF(Sheet2!$A$2:$A$10=B$1,Sheet2!$B$2:$B$10),0))

If you want to suppres #N/A occurrences, wrap the formula into an IFNA or IFERROR call:

=IFNA(INDEX(Sheet2!$C$2:$C$10,MATCH($A2,IF(Sheet2!$A$2:$A$10=B$1,Sheet2!$B$2:$B$10),0)),"")