IF statement paired with either a Index Match or Vlookup

GHustad

I have a scenario where I have an inventory tracking sheet that has service tag IDs in one column, employee names in another column, and then identification of device type in a third column (i.e. laptop, monitor, dock, etc.). Each row is unique to an asset, and therefor a given employee may appear in multiple rows (as they've been assigned multiple pieces of equipment.

I want to create a new sheet that flips the data so that each row is an employee, and the service tag IDs appear in a column for each type of device (column each for laptop vs monitor vs dock) as this will allow us to send them a single update request to confirm receipt of said equipment. I've tried with both an IF/Vlookup combo and a IF/Index combo and can't get either to work. Any thoughts? Here are my two attempts

=IF({Device Type} = "Laptop/Desktop", VLOOKUP([Allocated To]@row, {Service Tag}, 5, false), "") 

=IF({Device Type} = "Laptop/Desktop"),INDEX({Serial #/ST}, MATCH([Allocated To]@row, {Allocated To}, 0), "")

In both examples, {Device Type} is a reference to the Device Type column in the master sheet, Serial #/ST and Service Tag are actually the same...I just screwed up and named them differently, it is also a reference field to my master sheet. Allocated To appears in both the Master and the new sheet, as that's the "key" I'm trying to use to create the new format of data.

Krissia B.

Hello @GHustad

If I'm understanding correctly, when the Device Type is selected as Laptop/Desktop then you want to bring back the serial number for the Laptop/Desktop for the person it's assigned to at that row?

If so, what I think you may want to do use is an INDEX/COLLECT formula instead of an INDEX/MATCH formula because an INDEX/COLLECT formula allows you to add multiple criteria to pull in the information. In this case the criteria would be the device type and the user it's assigned to.

See screenshot sample below.

Source Data

Destination Data

Formula used in the Service Tag column:

• =INDEX(COLLECT({Service Tag}, {Employee}, Employee@row, {Device Type}, [Device Type]@row), 1)

If that doesn't resolve the issue, provide us screenshots (cover any sensitive data) on how the sheets are setup and more information so we can visually see what you're trying to accomplish.

More in the links below.

• Index Function
• Collect Function

Cheers,

Krissia

Krissia B.

Hello @GHustad

If I'm understanding correctly, when the Device Type is selected as Laptop/Desktop then you want to bring back the serial number for the Laptop/Desktop for the person it's assigned to at that row?

If so, what I think you may want to do use is an INDEX/COLLECT formula instead of an INDEX/MATCH formula because an INDEX/COLLECT formula allows you to add multiple criteria to pull in the information. In this case the criteria would be the device type and the user it's assigned to.

See screenshot sample below.

Source Data

Destination Data

Formula used in the Service Tag column:

• =INDEX(COLLECT({Service Tag}, {Employee}, Employee@row, {Device Type}, [Device Type]@row), 1)

If that doesn't resolve the issue, provide us screenshots (cover any sensitive data) on how the sheets are setup and more information so we can visually see what you're trying to accomplish.

More in the links below.

• Index Function
• Collect Function

Cheers,

Krissia

GHustad

@Krissia B Thank you so much! I had to tweak it slightly based on how my sheets are setup, but it is working. The last question I have is if there's a way to also embed an IF statement in there so that when a person isn't assigned a specific piece of equipment (i.e. a monitor) it returns a blank instead of #INVALID VALUE as it does now.

This is my current formula: =INDEX(COLLECT({Serial #/ST}, {Allocated To}, [Allocated To]@row, {Device Type}, "Laptop/Desktop"), 1)

So I guess the IF statement would be something like if formula above returns #INVALID VALUE than "", otherwise return the value from the formula above.

Krissia B.

Hello @GHustad

You're welcome! I'm glad that worked out. You can try something like the one below.

=IF(OR(ISBLANK([Device Type]@row), ISBLANK(Employee@row)), "", INDEX(COLLECT({Service Tag}, {Employee}, Employee@row, {Device Type}, "Laptop/Desktop"), 1))

So if the Device Type is blank or the Employee is blank, then just leave Service Tag blank. Hope this helps!

Cheers,

Krissia

GHustad

The issue isn't that the Device Type field itself is blank, the issue would be that there wouldn't be a row for that employee for that specific Device Type. So in the example below, this employee has been assigned 2 laptops and 1 dock, but no monitor. So my formula for the column where I'm trying to collect the Serial # for the Monitor returns #INVALID VALUE since there literally isn't a row in the data sheet that contains a Monitor for this employee.

This is what the sheet my formulas are being built in looks like. Where data is obscured, the formula is returning a Serial #.

GHustad

Actually I figured it out. IFERROR fixed it.

=IFERROR(INDEX(COLLECT({Serial #/ST}, {Allocated To}, [Allocated To]@row, {Device Type}, "Laptop/Desktop"), 1), "")