#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# index and Match

Options
edited 12/09/19

Hi Community

Im having an issue with index and match 4

i have imported from Excel

the sheet is set as follows

Item Name Vendor name 1  vendor rate 2 etc,

Example

Item #102 Rate on vendor 1 20.00 rate on vendor 2 25.00 then i have a coulmn calculating the best price =min on vendor 1-vendor 2

and by best name i want it to come the name of the vendor that is the Cheapest

so i did =INDEX(\$Certfied\$1:\$Levrage\$1, ,MATCH([Best Price]12,Certfied12:Levrage12,0))

if anybody can tell me what im doing worng ?

or if smart sheet dose not have this Formula?

Tags:

• ✭✭✭✭✭✭
Options

billing,

A picture might help (your formula example does not match the column names in the description) but I suspect that your collection (the first argument of INDEX()) is too small.

You also have two commas without an argument for argument 2.

What are you trying to return from your table?

Craig

• Options

• edited 02/07/17
Options

im not sure how to post a picture ? Here

• Options
• Options

what i am trying to do here that by the item name it should show the vendors name where we should Purchase based on who charges best rate

• ✭✭✭✭✭✭
edited 02/07/17
Options

How is [Best Price]1 determined?

Is that just a MIN() function (ignoring 0s) of the rest of the column?

Based on the rest of what you show in the image, I still don't get it.

Can you show a row with data that would have a [Best Price] and [Best Vendor} filled in (manually)?

Craig

ps: you spelled leverage wrong.

• edited 02/07/17
Options
1. yes best Price is Min Function

• edited 02/07/17
Options
• edited 02/07/17
Options
• ✭✭✭✭✭✭
Options

billing,

=INDEX(Certfied1:Levrage1, 1, MATCH([Best Price]12, Certfied12:Levrage12, 0))

Instead of a blank first argument, I added the row number (1)

That should do it.

Craig

• ✭✭✭✭✭✭
Options

Oh and I'm sure you realize that if two or more vendors have the same price, it will go with the one farthest to the left.

Craig

This discussion has been closed.