CODEWITHSUNDEEP
excel
LMK Web
LMK Web

Reputation: 111

How to make a VLOOKUP formula conditional in Excel?

I've got a large Excel workbook with multiple worksheets and copious amounts of data. Every third sheet is an amalgam of the previous two sets. I've set up the following formula to pull the data from each worksheet, add it together, and present it in the third worksheet:

=SUM(VLOOKUP($A7,'worksheet 1'!$A:$F,2,FALSE)+VLOOKUP($A7,'worksheet 2'!$A:$F,2,FALSE))

This works as expected, unless the column A value is in one of the source worksheets but not the other. Then, I get the #N/A error with "Value not available."

What I want to do is make it so that, if the column A value is not present in the first worksheet, excel ignores that VLOOKUP request and simply pulls in the data from the second worksheet (and vice-versa for a value in the #2 but not #1).

If VLOOKUP isn't the appropriate tool to use, I can change it. I had an example sheet that I used to build this query, and that's how they put it together.

I am not an Excel expert and I've never used Access, so I'm learning as I go. Any assistance is appreciated!

Upvotes: 1

Views: 174

Answers (1)

MDChaara
MDChaara

Reputation: 318

Since you are using numbers, you could actually use multiple sumif statements.

I would suggest something like:

=SUM(SUMIF('worksheet 1'!$A:$A,$A7,'worksheet 1'!$B:$B), SUMIF('worksheet 2'!$A:$A,$A7,'worksheet 2'!$B:$B))

SUMIF is faster, won't look in the entire range, and if it does not find the value in A7, it will simply return 0.

I hope this helps.

UPDATE

Simple example. Say we have 2 sheets with a total column and we would like to get the value of that total cell:

sheet1

sheet2

you could sum 2 VLOOKUP forumale, or you could use SUMIF as follows:

=SUM(SUMIF(Sheet1!$A:$A,$A$7,Sheet1!$B:$B),SUMIF(Sheet2!$A:$A,$A$7,Sheet2!$B:$B))

The above is simply saying: Look for the value in cell A7 in the range $A:$A in sheet1 and return what's in the same row in the range $B:$B, and do the same in sheet2, then sum them both.

Note that SUMIF will actually sum all the resutls in a range, so if you have 2 totals in Sheet1!$A:$A then it will sum them both up. In a way this is the opposite of what VLOOKUP will do: it will stop once the first row containing the value is found. SUMIF won't stop.

The desired result in the example above is 208 and that's what you shall get.

Upvotes: 2

Related Questions