Using IF to return cell contents in Google Sheets

1
votes

Edit for clarity:

The end result that I'm looking for will pass the name picked from the dropdown list in B1 into formulas below to check if each student has completed the training modules listed. The two sheets I'm looking at (for now) are 'Data' and 'Quick Reference' the rest are just placeholders.

I've looked and not found an answer so apologies if this has been asked before!

I'm trying to write a formula that will check form results and if it finds a match, return the contents from another column in that row.

The logical IF function works fine with hard coded entries, but not with cell contents.

So if I'm looking for "Deckhand USS Minnow I" I want to show the contents of Column H. Look on the Data and Quick Reference (Training Progress) tabs to see what I'm talking about.

This works: 

=IF(((VLOOKUP($B1,Data!C2:J20,4)="Build Coconut Phone I")),"Yes", "No")

This doesn't: 

=IF(((VLOOKUP($B1,Data!C2:J20,4)="Deckhand USS Minnow IV")),Data!H2:H20)

Google spreadsheet

1
google-sheets
See mcve - Rubén

1 Answers

0
votes

Short answer

Use QUERY() instead of IF(Vlookup(),)

=QUERY(Data!C1:J20,"Select H where F = 'Deckhand USS Minnow IV'",1)

Explanation

While could be possible to use an array formula using IF and VLOOKUP, QUERY() does the work in a direct and simple way.

The formula in the short answer will include the column heading. If you don't want it use

=QUERY(Data!C2:J20,"Select H where F = 'Deckhand USS Minnow IV'",0)

References