0

I have heard INDEX/MATCH is better to use across the board so I'm hoping this can be done with one of those functions, but I am having a heck of a time figuring it out on my own even though I've tried multiple things. I have a multi-sheet document. It is a list of approved fasteners so there are sheets for washers, nuts, screws, etc. I want to have a separate sheet to look up values based on the nominal size of the required fastener.

A1 on the working sheet will be where the nominal size is entered.

I need it to return multiple values from the washers sheet (we'll start with that one because once I have that, I can figure the rest out) because there will be numerous fasteners with the same nominal size. I also need it to ignore any rows where R exists in column J.

Basically,

If A1 on the working sheet = the value in column F on the WASHERS sheet (the column for nominal size) and there is no "R" in column J on the WASHERS sheet for that row, return the value from column C on the WASHERS sheet.

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
Traci L
  • 7
  • 3
  • 1
    No data, no idea of volumes. You're asking whether to use Index/Match Vs Vlookup... It's a pig in a poke. The real issue is probably to related to recalculation times. So, I suggest that you build your spreadsheet, or at least a prototype, then see how it performs. Then if it's not giving you the kind of performance you need, come back and raise it as a specific question. – Tedinoz Nov 16 '18 at 00:16
  • I was asking for help with the code. I wasn't sure which method would work better so I put both in the title. I wasn't able to figure it out either one by myself so that's why I asked here. – Traci L Nov 16 '18 at 17:59
  • Is this For Excel or Google Sheets? – Tedinoz Nov 16 '18 at 19:40
  • It's for Excel, sorry about that. – Traci L Nov 19 '18 at 21:19
  • Take it as given that Index/Match is "better" than VLOOKUP. [Excel-VLOOKUP vs. INDEX/MATCH-Which is better?](https://stackoverflow.com/questions/48348889/excel-vlookup-vs-index-match-which-is-better), [Excel-Match, Lookup or Index](https://stackoverflow.com/questions/50469552/excel-match-lookup-or-index/). I also liked mbaexcel.com's [Why INDEX MATCH is Better Than VLOOKUP](http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/)-it goes into detail that others simply brush over or ignore. – Tedinoz Nov 20 '18 at 21:14

1 Answers1

0

Use this formula:

=IFERROR(INDEX(A$1:A$14,SMALL(IF((B$2:B$14=F$2)*(C$2:C$14<>F$3),ROW(A$2:A$14)),ROW(1:1))),"")

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

This is how the formula related to data.

screenshot

Tedinoz
  • 5,911
  • 3
  • 25
  • 35