CODEWITHSUNDEEP
excelif-statementexcel-formulavlookup
user9351236
user9351236

Reputation: 143

How to combine vlookup and if else in Excel using formulas

I am trying to update the budget of resources for travelling in sheet 1 with their per day expenses in sheet2. I have to first verify the destination city, and then fetch the expenses of their travel from sheet 2 on the basis of the number of days they are travelling from (mentioned in sheet 1). So, First the destination should be matched , once we get the row number (from sheet 2), then need to fetch the number of days from sheet 1, and on the basis of the number of days, fetch the expense from sheet 2

Sheet 1

Destination        No of Days     Total expenses(output) 
City 1               1              150 
City 2              3.5             200 
City 3               2              400

Sheet 2

Destination  Day 1  Day 2   Day 3   Day 4 
City 2       100    150     175    200   
City 1       150    250     350    450   
City 3       200    400     600    800

I tried using vlookup, and nested if formula each, but I am not able to fetch the number of days

(IF(A2=Sheet2!$A$2,Sheet2!$B$2,IF(A3=Sheet2!$A$3,Sheet2!$B$3,IF(A4=Sheet2!$B$4,Sheet2!$B$4))))

The outcome is mentioned in the description. The column Total expenses in sheet 1 is the expected output.

Upvotes: 0

Views: 63

Answers (2)

Error 1004
Error 1004

Reputation: 8220

Additionally you can use:

=SUMPRODUCT((Sheet2!$A$2:Sheet1!$A$4=A2)*(Sheet2!$B$1:$E$1="Day " & ROUNDUP(B2,0)),Sheet2!$B$2:$E$4)

Upvotes: 1

Zac
Zac

Reputation: 1944

Instead of VLookUp, try Index + Match

=INDEX(Sheet2!$B$2:$E$4,MATCH($A2,Sheet2!$A$2:$A$4,0),$B2)

Upvotes: 3

Related Questions