Google Sheets =IF formula working sporadically across multiple sheets

0
votes

I have a workbook with multiple sheets that I am trying to transpose information in between sheets.

I am using an =IF formula to identify instances in which there is a data match in Sheet1 and to pull information from the header of the corresponding column into Sheet2.

Before anyone mentions, I've tried this using the VLOOKUP function which unfortunately only works on numerical data (my set is alphabetical).

Unfortunately the formula I am using only seems to be working sporadically - although in the instances in which it does work, it works perfectly and as intended. In all other instances it returns either a #VALUE! error or fails to recognise the logic function as true.

=IF(Sheet1!$B$3:$B$46=Sheet2!B$1, Sheet1!$C$2:$AB$2, "-")

The live version of the formula is linked here (Google Docs).

How can I make the formula work for all cells? Would greatly appreciate any and all help, thank you!

1
google-sheetsgoogle-sheets-formula
Who says VLOOKUP only works on Numerical Data? That is an incorrect assumption. VLOOKUP works on all data types, they just need to match. =VLOOKUP(Sheet2!B$1,Sheet1!$B$3:C$46,2,FALSE) will return the value in C where B matches. – Scott Craner
Apologies @ScottCraner for the error. However I tried it with the VLOOKUP and it is now returning results from Sheet2!B1:J1 instead of returning Sheet1!B2:J2 (updated the live sheet with an example). – Cheryl Kara
Try Filter: =FILTER(Sheet1!$C$2:$AB$2,Sheet1!$B$3:$B$46=Sheet2!B$1) – Scott Craner
pls include some example of desired output – player0

1 Answers

0
votes

I was able to populate the entire range using the ArrayFormula. However, I don't understand the underlying logic behind your spreadsheet so this might be not what you are looking for. 

=ArrayFormula(IF(Sheet1!$B$3:$AB=Sheet2!B$1:AB$1, Sheet1!$B$2:$AB$2, "-"))

Long story short, the IF formula doesn't return multiple values - you must wrap it with the ArrayFormula to execute against the entire range.

enter image description here