Searching across three columns and if match found, then return info from a date column.

Dan Kadushin

So I'm attempting to create an inventory rental sheet where the end users (front desk techs) can enter information on a "Checkout" sheet, and that information can be checked by our main Asset inventory sheet and return the checkout date to the main Asset inventory sheet. Problem is we have multiple types of rental assets, and to add them appropriately to the Checkout sheet they need to be entered into four different columns, "Cameras, Support, Audio, Lighting". I was originally going to try to use a vlookup with helper columns on the main Asset inventory sheet and =max those returns, but I feel like there's a cleaner way of doing it with a next INDEX/MATCH but I'm not familiar enough with it.

Was planning on putting the formula in the [Assignee Check-out Date] column on the Asset sheet, that checks that row's Barcode (column 1 on Asset), across the [Camera Barcode], [Support Barcode], [Lighting Barcode], and [Audio Barcode] on the Check Out sheet and returns the date in the [Check Out Date] from column 6 on the Check Out sheet to the [Assignee Check Out Date] on the Asset sheet.

Any suggestions? Thanks!

Paul Newcome

I missed that the Asset Type was already on the Asset sheet. In that case we can ignore #2 and #3. That really simplifies things.

I will start with a breakdown of the INDEX/MATCH and then we can get into the creative side of things.

=INDEX(range_to_pull_from, row_number, optional_column_number)

The first and third sections of the INDEX function are easy. We already know the range we want to pull from is {Form Sheet Checkout Date Column}. Since we are only referencing a single column, we don't need to even enter anything in the third portion. S now we have...

=INDEX({Form Sheet Checkout Date Column}, row_number)

How do we automate which row number to pull from? Well that's where the MATCH function comes into play. MATCH will output a number based on where within a range the match is found. If the range is looking down a single column, that number will be the row number.

=MATCH(data_to_match_on, range_to_match_in, 0)

Again we have an easy first and third section. First section is going to be matching on the barcode and third section we are going to leave as zero for an exact match.

=MATCH(Barcode@row, range_to_match_in, 0)

Now for the creative part. We want the MATCH function to search across four separate columns depending on the Asset Type. So we use a nested IF statement to determine the "range_to_match_in".

IF([Asset Type]@row = "Camera", {Form Sheet Camera Barcode Column}, IF([Asset Type]@row = "Support", {Form Sheet Support Barcode Column}, IF([Asset Type]@row = "Lighting", {Form Sheet Lighting Barcode Column}, {Form Sheet Audio Barcode Column})))

Great! Now we have our range for our MATCH function. Let's plug that in:

=MATCH(Barcode@row, IF([Asset Type]@row = "Camera", {Form Sheet Camera Barcode Column}, IF([Asset Type]@row = "Support", {Form Sheet Support Barcode Column}, IF([Asset Type]@row = "Lighting", {Form Sheet Lighting Barcode Column}, {Form Sheet Audio Barcode Column}))), 0)

Now that our MATCH function is complete so that we can generate a row number, we can drop this into the second portion of our INDEX function and wrap that up too:

=INDEX({Form Sheet Checkout Date Column}, MATCH(Barcode@row, IF([Asset Type]@row = "Camera", {Form Sheet Camera Barcode Column}, IF([Asset Type]@row = "Support", {Form Sheet Support Barcode Column}, IF([Asset Type]@row = "Lighting", {Form Sheet Lighting Barcode Column}, {Form Sheet Audio Barcode Column}))), 0))

And there you have it. The INDEX/MATCH will stop on the first match which is why we reversed the order of the From Sheet. The first barcode match is going to be the most recent entry, so we don't have to write anything additional to get it to filter out the old entries.

Paul Newcome

Are you using forms to check out the inventory?

Dan Kadushin

Sort of. The renters will enter their desired equipment in a form which will populate in the checkout sheet (I haven't built that part yet), but our techs will assign the barcode to the Camera, Support, Audio and Lighting columns as they physically check the equipment out to the renter.

Dan Kadushin

Sorry, forgot to mention, the form will also populate the Assignee, Assignee Email, and Phone number columns.

Paul Newcome

So that first screenshot is being populated via form?

Dan Kadushin

Correct.

Paul Newcome

And you want the most recent date based on barcode?

Dan Kadushin

Yes sir. Your line of questioning has me worried that I've either created something unworkably complex or I've missed something astoundingly simple...

Paul Newcome

No worries at all. You are somewhere in the middle. All we need is a little creativity, and I will walk you through each step along the way. Are you open to a little bit of restructuring (listed below)?

1. Sorting the existing form entries to be in descending order based on the Created Date and switching forms to populate at the top of the sheet
2. Creating a two column list of all asset barcodes and the associated type (camera/support/lighting/audio)
3. Inserting a text/number helper column on the Asset sheet

Dan Kadushin

Absolutely. Number 1 is all set now.

Where does #2 have to go, checkout (form submission sheet), the asset sheet or a separate sheet? Currently all barcodes and asset type are on the asset sheet.

Should I call the helper column anything in particular?

Paul Newcome

I missed that the Asset Type was already on the Asset sheet. In that case we can ignore #2 and #3. That really simplifies things.

I will start with a breakdown of the INDEX/MATCH and then we can get into the creative side of things.

=INDEX(range_to_pull_from, row_number, optional_column_number)

The first and third sections of the INDEX function are easy. We already know the range we want to pull from is {Form Sheet Checkout Date Column}. Since we are only referencing a single column, we don't need to even enter anything in the third portion. S now we have...

=INDEX({Form Sheet Checkout Date Column}, row_number)

How do we automate which row number to pull from? Well that's where the MATCH function comes into play. MATCH will output a number based on where within a range the match is found. If the range is looking down a single column, that number will be the row number.

=MATCH(data_to_match_on, range_to_match_in, 0)

Again we have an easy first and third section. First section is going to be matching on the barcode and third section we are going to leave as zero for an exact match.

=MATCH(Barcode@row, range_to_match_in, 0)

Now for the creative part. We want the MATCH function to search across four separate columns depending on the Asset Type. So we use a nested IF statement to determine the "range_to_match_in".

IF([Asset Type]@row = "Camera", {Form Sheet Camera Barcode Column}, IF([Asset Type]@row = "Support", {Form Sheet Support Barcode Column}, IF([Asset Type]@row = "Lighting", {Form Sheet Lighting Barcode Column}, {Form Sheet Audio Barcode Column})))

Great! Now we have our range for our MATCH function. Let's plug that in:

=MATCH(Barcode@row, IF([Asset Type]@row = "Camera", {Form Sheet Camera Barcode Column}, IF([Asset Type]@row = "Support", {Form Sheet Support Barcode Column}, IF([Asset Type]@row = "Lighting", {Form Sheet Lighting Barcode Column}, {Form Sheet Audio Barcode Column}))), 0)

Now that our MATCH function is complete so that we can generate a row number, we can drop this into the second portion of our INDEX function and wrap that up too:

=INDEX({Form Sheet Checkout Date Column}, MATCH(Barcode@row, IF([Asset Type]@row = "Camera", {Form Sheet Camera Barcode Column}, IF([Asset Type]@row = "Support", {Form Sheet Support Barcode Column}, IF([Asset Type]@row = "Lighting", {Form Sheet Lighting Barcode Column}, {Form Sheet Audio Barcode Column}))), 0))

And there you have it. The INDEX/MATCH will stop on the first match which is why we reversed the order of the From Sheet. The first barcode match is going to be the most recent entry, so we don't have to write anything additional to get it to filter out the old entries.

Dan Kadushin

Thank you so much for taking the time to explain all of that! It's very helpful to have the breakdown! It worked like a charm! I added an IFERROR statement to the front to return a blank cell if the barcode didn't exist in the checkout sheet. (I had other formulas based on if a cell was blank or not on the asset sheet.)

=IFERROR(INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, IF([Asset Type]@row = "Camera", {Asset Checkout Range 1}, IF([Asset Type]@row = "Support", {Asset Checkout Range 2}, IF([Asset Type]@row = "Lighting", {Asset Checkout Range 5}, {Asset Checkout Range 6}))), 0)), " ")

Thank you again, Paul!

Paul Newcome

I'm glad it all made sense. Happy to help. 👍️