?Vlookup?

Drew Cooper

Trying to create a Grid to help me identify deltas from 2 other grids.

First Grid is called "Forecast", second grid is called "Actual"

Column A on both Grids is Account Name

Column B on both Grids is Quantity

In my NEW Grid called Delta - I would like to be able to compare the quantiles from column B and match them to column A. Very much like a V-look up (I believe).

Example:

Forecast Sheet

Column A (Account Names)

Walmart

Amazon

Apple

Google

Column B (Qty)

1

2

3

4

Actual Sheet

Column A (Account Names)

Google

Apple

Amazon

Walmart

Column B

4

3

2

1

Goal would be to have a grid match the results.

Column A (Account Name)

*don't care about the order

Walmart

Amazon

Apple

Google

Column B

*Qty from column B in Forecast Sheet

1 (walmart)

2 (amazon)

3 (Apple)

4 (google)

Column C

*Qty from Column B in Actual Sheet matched by name

Walmart (4)

Amazon (3)

Apple (2)

Google (1)

Then I would be able to say we forecasted Walmart for 1 but their actual is 4, Amazon for 2 but actual is 3, etc…..

KPH

You can add a column to sheet A and use INDEX MATCH to pull in the quantity from sheet B where the name matches that on the current row in sheet A. INDEX MATCH is more flexible than VLOOKUP but will do a similar thing.

This formula will do that

=INDEX({column B in Forecast Sheet}, MATCH([Account Names]@row, {account names in forecast sheet}, 0))

Where

{column B in Forecast Sheet} and {account names in forecast sheet} are cross sheet references

If you wrap the formula with an IFERROR you can return 0 instead of NO MATCH when a match is not found.

=IFERROR(INDEX({column B in Forecast Sheet}, MATCH([Account Names]@row, {account names in forecast sheet}, 0)), 0)

Here is an example

This is sheet B

Drew Cooper

I also won't have the list of names readily available - so I would need to be able to account for it referencing the names in Column A on sheet 1. So look at column A in sheet one (Account Names) and match them to column A in sheet 2. If there is no match on sheet 2 return a 0. Example: might have forecasted "Starbucks" for 5 but there is no Starbucks in the actual. So I would get 5 for Forecast and 0 for actual which would allow me to find the delta of "5".

KPH

You can add a column to sheet A and use INDEX MATCH to pull in the quantity from sheet B where the name matches that on the current row in sheet A. INDEX MATCH is more flexible than VLOOKUP but will do a similar thing.

This formula will do that

=INDEX({column B in Forecast Sheet}, MATCH([Account Names]@row, {account names in forecast sheet}, 0))

Where

{column B in Forecast Sheet} and {account names in forecast sheet} are cross sheet references

If you wrap the formula with an IFERROR you can return 0 instead of NO MATCH when a match is not found.

=IFERROR(INDEX({column B in Forecast Sheet}, MATCH([Account Names]@row, {account names in forecast sheet}, 0)), 0)

Here is an example

This is sheet B

Drew Cooper

So this worked perfectly! My situation has slightly changed tho. How do you go about doing additional matching.

So for example:

What if there was a column C in your example that had further descriptions.

3 Columns

A.) Orbital City B.)12 C.)Red

A.) Orbital City B.) 4 C.) Blue

How do I get it to match not only Orbital City but only also match the 12 to the Red, and 4 to the blue if both of those "Color" Column was also a matchable field from the original sheet? The problem is its currently thinking anything with Orbital City should return the 12.

Drew Cooper

This is what I thought would work…or at least trying to accomplish. How do I force it to match 2 things. Tried the And function with no luck.

Original Formula working for 1 match:

=IFERROR(INDEX({ACTUAL - CURRENT MONTH Range 1}, MATCH([Name (1)]@row, {ACTUAL - CURRENT MONTH Range 2}, 0)), 0)

Failed Attempt at formula to require 2 matches:

=IFERROR(INDEX({ACTUAL - CURRENT MONTH Range 1}, MATCH([Name (1)]@row, {ACTUAL - CURRENT MONTH Range 2}, AND(MATCH([Product Group (1)]@row, {ACTUAL - CURRENT MONTH Range 3}, 0)), 0)))

KPH

Yay! I will count that as a win if it worked for the original brief 😀

As for the new requirements. You can still use INDEX but instead of MATCH use a COLLECT function to return the row based on more than one piece of logic.

Forecast sheet now looks like:

This formula in Qty from column B in Forecast sheet:

=IFERROR(INDEX(COLLECT({column B in Forecast Sheet}, {account names in forecast sheet}, [Account Names]@row, {column c in forecast sheet}, [2nd thing to look for]@row), 1), 0)

Gives:

Drew Cooper

Its close - but maybe this helps explain it better.

KPH

I think it works - just different column names/order.

In my example, this is sheet 1

This is sheet 2

The column you are now calling D in sheet 1 is the value from column that is now F in sheet 2