Return a value by using MATCH, INDEX or VLOOKUP with paired search terms

0
votes

I'm trying to return a value based on whether other certain values are in a row. I have two sheets in Excel that I'm working on and can't get correct formula to return a value using VLOOKUP, INDEX or MATCH. The following is an example of the spreadsheets I have:

Sheet 1
Segment         Node           Value

ACDC            Acadian          300
Ventura         Acadian           50
LooseGoose      Acadian          600
Dave            Haynes           500

Sheet 2
Segment         Node           Value
ACDC            Acadian

So I'm trying to write a function that'll return the value in Sheet 2 if the segment and node names match each other compared to how they are in Sheet 1.

1
vbaexcelmatchvlookup
Its suppose to look like this, it didn't come out right. – user3870159
I'm working in Excel. I have any empty column that I'm trying to write a formula for, where I return the value in Sheet 1 based on whether the segment and node names match in Sheet 2 compared to whats in Sheet 1 already. – user3870159

1 Answers

1
votes

Assuming Segment is in A1 of both sheets, insert a new ColumnA in Sheet 1 with: 

=B2&"|"&C2

in A2 copied down to suit, then in C2 of Sheet 2: 

=VLOOKUP(A2&"|"&B2,'Sheet 1'!A:D,4,0)

copied down to suit.