Index Match Help

[morerockin]

Hello All,

See attached. Book2 (7).xlsx

For some reason, I cannot get it to index/match the string of values in column B on the first tab with column C on the 2nd tab. It doesn't seem to like it when I have added a letter to the string. I need column R on the first sheet to return values on the second sheet.

Example. Sheet 1. Site ID 3 has no issue returning values.

[newdoverman]

If you add a letter to the string on Daily Pass - Fail Report it has to match exactly the string in the formula unless you use a wildcard match.

An example of a wildcard match formula is:

=MATCH("*"&"Permanent Acceptance"&"*",D2:D10,0)

This will find Permanent Acceptance with anything before and after that string. This is what you will have to have if you want to be able add a letter as a prefix to the values in column D of Daily Pass - Fail Report worksheet.

I think that this is what you are looking for for column R of Final Acceptance Tracker. Enter with Ctrl + Shift + Enter

Please Login or Register  to view this content.

[morerockin]

Hey Newdoverman,

OOPS . . I just saw your update!

OLD POST BELOW

I shouldn't have to use a wildcard because I've asked the formula to return an exact match but it is not complying as I have jacked something up and am about to go crazy!

[newdoverman]

See the updated post. I have used the wildcard and replaced the long IFERROR statement with "". You will see in the file that I enclosed that it does work and is much shorter than the original.

[morerockin]

See the updated post. I have used the wildcard and replaced the long IFERROR statement with "". You will see in the file that I enclosed that it does work and is much shorter than the original.

Thanks Newdoverman!

Row 2 in column R on the first sheet should be returning values as well. It isn't because it appears the formula is missing verbiage for: Permanent Checklist. Ideally, the formula would return data if column F on the 2nd sheet has "Permanent Acceptance" or "Permanent Checklist".

[newdoverman]

You can change the formula to accept anything with Permanent in column D just by removing the word Acceptance...like this:

Please Login or Register  to view this content.

Remember that this is an ARRAY FORMULA so enter with Ctrl + Shift + Enter

You now know how to construct the wildcard so you can use that construction in the IFERROR section to include other criteria that you may want.

[morerockin]

So . . on my master spreadsheet with 10K row's the word "Acceptance" is used two different times (Permanent Acceptance and Permanent Acceptance TEMP) as is the word checklist (similar scenario's). I would like to have one formula to drag down column R that returns values ONLY if it see's "Permanent Acceptance" or "Permanent Checklist".

Is this possible?

[newdoverman]

If there are no other possibilities with the Permanent Acceptance and Permanent Checklist, this will work.

Please Login or Register  to view this content.

[morerockin]

It is still returning values for "Permanent Acceptance TEMP" on my master tracker. If the string doesn't contain ONLY "Permanent Acceptance", it should return blanks/#N/A.

Can we set this to only return a value if it matches the specific string "Permanent Acceptance"?

[newdoverman]

This will return Permanent Acceptance and as well as Permanent Checklist and only those values if that is all that is in the cells in question. This was done by removing the wildcards.

Please Login or Register  to view this content.